APPS.CZ_ORAAPPS_INTEGRATE SQL Statements

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

Line: 65

		SELECT SUBSTR(nSURR_KEY,((INSTR(nSURR_KEY,':')+1)))
			INTO nORG_ID
			FROM DUAL;

Line: 68

		SELECT RTRIM(nSURR_KEY,nORG_ID)
			INTO nITEM_ID
			FROM DUAL;

Line: 71

		SELECT RTRIM(nITEM_ID,':')
			INTO nITEM_ID
			FROM DUAL;

Line: 154

PROCEDURE delete_spx_process
(p_short_name  IN VARCHAR2,
 p_application IN VARCHAR2) AS
BEGIN
fnd_program.delete_program(p_short_name,p_application);

Line: 159

fnd_program.delete_executable(p_short_name,p_application);

Line: 225

                    'SellingPoint : Order Export Update Status Process',
                    'SPOEEXPUS',
                    application_name,
                    'Update Status of Exported Orders from SellingPoint to OE',
                    'CZ_EXPORT.ORDER_STATUS_UPDATE_CP',
                    request_group,
                    cz_schema);

Line: 234

     LOG_REPORT('Error : < SellingPoint Order Export Update Status Process > REGISTRATION');

Line: 253

                    'SellingPoint : Customer Export Update Status',
                    'SPAREXPUS',
                    ar_application_name,
                    'Update Status of Exported Customers from SellingPoint to OE',
                    'CZ_EXPORT.CUSTOMER_STATUS_UPDATE_CP',
                    ar_request_group,
                    cz_schema);

Line: 262

     LOG_REPORT('Error : < SellingPoint Customer Export Update Status Process > REGISTRATION');

Line: 341

                      default_value=>'select :$FLEX$.CZ_ORG_ID from dual',
                      display=>'N',
                      display_size=>10,
                      description_size=>10,
                      concatenated_description_size=>50,
                      prompt=>'dsORG_ID');

Line: 602

PROCEDURE delete_bom_sync_process
(application_name IN VARCHAR2 -- default 'Oracle Configurator'
) AS
BEGIN
    BEGIN
        fnd_program.delete_program('CZBOMSIM',application_name);

Line: 608

        fnd_program.delete_executable('CZBOMSIM',application_name);

Line: 617

        fnd_program.delete_program('CZALLBOMSIM',application_name);

Line: 618

        fnd_program.delete_executable('CZALLBOMSIM',application_name);

Line: 627

        fnd_program.delete_program('CZALLBOMSYNC',application_name);

Line: 628

        fnd_program.delete_executable('CZALLBOMSYNC',application_name);

Line: 826

register_spx_process('Select Tables to Be Imported',
                     'CZREPSIMP',
                     application_name,
                     'Select Tables to Be Imported',
                     'CZ_ORAAPPS_INTEGRATE.SETTABLEIMPORT',
                     request_group,
                     cz_schema);

Line: 866

     LOG_REPORT('Error : < Select Tables to Be Imported > REGISTRATION');

Line: 913

PROCEDURE delete_service_process
(application_name IN VARCHAR2 -- default 'System Administration'
) AS
BEGIN
BEGIN
    fnd_program.delete_program('SPREPGS',application_name);

Line: 919

    fnd_program.delete_executable('SPREPGS',application_name);

Line: 928

    fnd_program.delete_program('SPREPIN',application_name);

Line: 929

    fnd_program.delete_executable('SPREPIN',application_name);

Line: 938

    fnd_program.delete_program('SPREPGIMP',application_name);

Line: 939

    fnd_program.delete_executable('SPREPGIMP',application_name);

Line: 948

    fnd_program.delete_program('SPREPSIMP',application_name);

Line: 949

    fnd_program.delete_executable('SPREPSIMP',application_name);

Line: 958

    fnd_program.delete_program('SPCREATELNK',application_name);

Line: 959

    fnd_program.delete_executable('SPCREATELNK',application_name);

Line: 968

    fnd_program.delete_program('SPPUBLISHMODEL',application_name);

Line: 969

    fnd_program.delete_executable('SPPUBLISHMODEL',application_name);

Line: 978

    fnd_program.delete_program('SPPOPULATESRV',application_name);

Line: 979

    fnd_program.delete_executable('SPPOPULATESRV',application_name);

Line: 996

PROCEDURE delete_export_process
(application_name IN VARCHAR2 -- default 'Oracle Order Entry'
) AS
ar_application_name VARCHAR2(50):='Oracle Receivables';

Line: 1002

    fnd_program.delete_program('SPOEEXP',application_name);

Line: 1003

    fnd_program.delete_executable('SPOEEXP',application_name);

Line: 1012

    fnd_program.delete_program('SPOEEXPUS',application_name);

Line: 1013

    fnd_program.delete_executable('SPOEEXPUS',application_name);

Line: 1024

fnd_program.delete_program('SPAREXP',ar_application_name);

Line: 1025

fnd_program.delete_executable('SPAREXP',ar_application_name);

Line: 1034

fnd_program.delete_program('SPAREXPUS',ar_application_name);

Line: 1035

fnd_program.delete_executable('SPAREXPUS',ar_application_name);

Line: 1044

fnd_program.delete_program('SPAREXPSC',ar_application_name);

Line: 1045

fnd_program.delete_executable('SPAREXPSC',ar_application_name);

Line: 1054

fnd_program.delete_program('SPOEEXPSO',application_name);

Line: 1055

fnd_program.delete_executable('SPOEEXPSO',application_name);

Line: 1072

PROCEDURE delete_import_process
(application_name IN VARCHAR2 -- default 'Oracle Bills of Material'
) AS
BEGIN
BEGIN
    fnd_program.delete_program('SPAPPIMP','System Administration');

Line: 1078

    fnd_program.delete_executable('SPAPPIMP','System Administrationdel');

Line: 1086

    fnd_program.delete_program('SPAPPIMPPCM',application_name);

Line: 1087

    fnd_program.delete_executable('SPAPPIMPPCM',application_name);

Line: 1096

    fnd_program.delete_program('SPAPPIMPRFCM',application_name);

Line: 1097

    fnd_program.delete_executable('SPAPPIMPRFCM',application_name);

Line: 1106

    fnd_program.delete_program('SPAPPIMPRMCM',application_name);

Line: 1107

    fnd_program.delete_executable('SPAPPIMPRMCM',application_name);

Line: 1214

stmt:='SELECT setting_id,value from '||var_schema||'CZ_DB_SETTINGS WHERE section_name like '''||section_name||
''' and setting_id like '''||setting_id||''' ';

Line: 1254

  SELECT SECTION_NAME,SETTING_ID,VALUE,DESC_TEXT FROM CZ_DB_SETTINGS
  WHERE SECTION_NAME LIKE LIKE_SectionName AND SETTING_ID LIKE LIKE_SettingID;

Line: 1291

SELECT 'X' FROM cz_db_settings
WHERE section_name=section_Name AND
setting_id=sSetting_Id;

Line: 1301

   UPDATE cz_db_settings SET value=sValue WHERE section_name=sSection_Name AND setting_id=sSetting_Id;

Line: 1303

   INSERT INTO cz_db_settings(setting_id,section_name,data_type,value,desc_text)
   VALUES(sSetting_Id,sSection_Name,sType,sValue,sDESCRIPTION);

Line: 1321

  SELECT DST_TABLE,XFR_GROUP,DISABLED FROM CZ_XFR_TABLES
  WHERE DST_TABLE LIKE LIKE_DstTableName AND XFR_GROUP LIKE LIKE_PhaseName;

Line: 1355

 UPDATE CZ_XFR_TABLES SET
   DISABLED=DECODE(UPPER(EnableImport),'0','1','OFF','1','N','1','DISABLE','1',
   '1','0','ON','0','Y','0','ENABLE','0',DISABLED)
 WHERE DST_TABLE=DstTableName AND XFR_GROUP LIKE LIKE_PhaseName;

Line: 1377

  EXECUTE IMMEDIATE 'SELECT NULL FROM DUAL@' || p_link_name;

Line: 1378

  SELECT NULL INTO v_null FROM user_db_links WHERE UPPER(db_link) = UPPER(p_link_name)
      OR UPPER(db_link) LIKE UPPER(p_link_name) || '.%';

Line: 1405

    EXECUTE IMMEDIATE 'SELECT sysdate FROM dual'||v_db_link
    INTO v_temp;

Line: 1418

    FOR i IN (SELECT object_name FROM user_objects WHERE object_name like p_filter AND
              object_type='PACKAGE BODY' AND status='INVALID')
    LOOP
       BEGIN
           EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE BODY';

Line: 1488

	   SELECT fndnam_link_name INTO v_fndnam_link_name
         FROM cz_servers
         WHERE local_name = slocal_name;

Line: 1508

        SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
                                    '0', 0, 'OFF', 0, 'N', 0, 'NO',  0,'FALSE', 0, 'DISABLE', 0,
                                     0) --default value
          INTO settingVal FROM cz_db_settings
         WHERE UPPER(section_name) = 'IMPORT'
           AND UPPER(setting_id) = 'USELOCALTABLEINEXTRACTIONVIEWS';

Line: 1526

			'	SELECT ORGANIZATION_ID,ASSEMBLY_ITEM_ID,ASSEMBLY_TYPE,ALTERNATE_BOM_DESIGNATOR,COMMON_BILL_SEQUENCE_ID,BILL_SEQUENCE_ID  ' ||
			'	FROM BOM_BILL_OF_MATERIALS' || v_fndnam_link_name ||' WHERE ALTERNATE_BOM_DESIGNATOR IS NULL'||v_rownum;

Line: 1532

					SELECT ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME
					FROM ORG_ORGANIZATION_DEFINITIONS'|| v_fndnam_link_name;*/

Line: 1536

    					'SELECT A.organization_id ORGANIZATION_ID, b.organization_code ORGANIZATION_CODE, A.name ORGANIZATION_NAME  ' ||
  					'FROM hr_all_organization_units'|| v_fndnam_link_name || ' A,  ' ||
					'	mtl_parameters'|| v_fndnam_link_name || ' b,  ' ||
  					'	hr_organization_information'|| v_fndnam_link_name || ' c, ' ||
  					'	hr_organization_information'|| v_fndnam_link_name || ' c1,  ' ||
					'	gl_sets_of_books'|| v_fndnam_link_name || ' gsob ' ||
  					'WHERE A.organization_id = b.organization_id ' ||
  					'AND A.organization_id = c.organization_id ' ||
    					'AND A.organization_id = c1.organization_id ' ||
  					'AND c.org_information1 = ''INV'' ' ||
  					'AND  c.ORG_INFORMATION2 = ''Y''  ' ||
  					'AND ( c.ORG_INFORMATION_CONTEXT || '''') = ''CLASS'' ' ||
 					'AND ( c1.ORG_INFORMATION_CONTEXT || '''') =''Accounting Information''  ' ||
 					'AND c1.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)'||v_rownum;

Line: 1561

	v_string := 'select count(*) from all_tab_columns'||v_fndnam_link_name||
			' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%''
				and column_name = ''INDIVISIBLE_FLAG''';

Line: 1597

	v_string := 'select distinct column_name from all_tab_columns'||v_fndnam_link_name||
			' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%'' and column_name = ''COMMS_NL_TRACKABLE_FLAG''';

Line: 1610

	v_string := 'select distinct table_name from all_tables'||v_fndnam_link_name||
			' where owner='''||v_inv_oracle_schema||''' AND table_name = ''MTL_SYSTEM_ITEMS_TL'' ';

Line: 1623

                'SELECT distinct T.DESCRIPTION, T.LANGUAGE, T.SOURCE_LANG,  ' ||
                'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
                'B.TOP_ITEM_ID, B.EXPLOSION_TYPE, BBM.ORGANIZATION_ID,  ' ||
                'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
            	'FROM BOM_EXPLOSIONS' || v_fndnam_link_name || ' B, ' ||
            	'BOM_BILL_OF_MATERIALS' || v_fndnam_link_name || ' BBM,  ' ||
                'MTL_SYSTEM_ITEMS_TL' || v_fndnam_link_name || ' T ' ||
            	'WHERE T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
                'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
                'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
                'AND BBM.ASSEMBLY_ITEM_ID = B.TOP_ITEM_ID ' ||
                'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
            	'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
                'AND B.EXPLOSION_TYPE = ''OPTIONAL'' ' ||
                'AND T.LANGUAGE IN (SELECT language_code  ' ||
                                 '  FROM fnd_languages' || v_fndnam_link_name ||
                                 ' WHERE installed_flag IN (''B'', ''I''))';

Line: 1648

                'SELECT distinct T.DESCRIPTION, F.LANGUAGE_CODE, F.LANGUAGE_CODE AS SOURCE_LANG, ' ||
                'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID,  ' ||
                'B.TOP_ITEM_ID, B.EXPLOSION_TYPE, BBM.ORGANIZATION_ID,  ' ||
                'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
            	'FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
            	'BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM,  ' ||
                'MTL_SYSTEM_ITEMS'|| v_fndnam_link_name || ' T, ' ||
                'FND_LANGUAGES'|| v_fndnam_link_name || ' F ' ||
            	'WHERE T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
                'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
                'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
                'AND BBM.ASSEMBLY_ITEM_ID = B.TOP_ITEM_ID ' ||
                'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
            	'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
                'AND B.EXPLOSION_TYPE = ''OPTIONAL'' ' ||
                'AND F.INSTALLED_FLAG in (''B'', ''I''))';

Line: 1673

	v_string := 'select distinct column_name from all_tab_columns' || v_fndnam_link_name ||
			' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%''  ' ||
			' and column_name = ''CONFIG_MODEL_TYPE''';

Line: 1684

				    ' SELECT M.INVENTORY_ITEM_ID,M.ORGANIZATION_ID,SEGMENT1,BOM_ITEM_TYPE, ' ||
 				    ' FIXED_LEAD_TIME,START_DATE_ACTIVE,END_DATE_ACTIVE, ' ||
	 			    ' CUSTOMER_ORDER_ENABLED_FLAG, '||v_desc_tl||
 				    ' INVENTORY_ITEM_STATUS_CODE,ITEM_CATALOG_GROUP_ID,CONCATENATED_SEGMENTS,' || v_versioned_string ||
				    ' FROM MTL_SYSTEM_ITEMS_KFV' || v_fndnam_link_name|| ' M ' ||v_mtl_system_items_tl||
                                      v_where_tl_2||v_where_lang_tl||v_rownum;

Line: 1693

					'SELECT ' ||
 						'M.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,M.SEGMENT1, ' ||
 						'M.FIXED_LEAD_TIME,M.START_DATE_ACTIVE,M.END_DATE_ACTIVE, ' ||
	 					'M.CUSTOMER_ORDER_ENABLED_FLAG, '||v_desc_tl||
 					      ' M.INVENTORY_ITEM_STATUS_CODE,M.ITEM_CATALOG_GROUP_ID,' || v_versioned_string ||
                                    ',M.CONCATENATED_SEGMENTS, ' ||
 				'		BBM.ASSEMBLY_ITEM_ID AS TOP_ITEM_ID,B.EXPLOSION_TYPE, ' ||
                                '                B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID,B.COMPONENT_ITEM_ID, ' ||
 				'		B.PLAN_LEVEL,B.SORT_ORDER,B.CREATION_DATE,B.CREATED_BY,B.LAST_UPDATE_DATE, ' ||
	 			'		B.LAST_UPDATED_BY,B.OPTIONAL,B.MUTUALLY_EXCLUSIVE_OPTIONS,B.LOW_QUANTITY, ' ||
				'		B.HIGH_QUANTITY,B.COMPONENT_QUANTITY,B.PRIMARY_UOM_CODE,B.BOM_ITEM_TYPE, ' ||
				'		B.PICK_COMPONENTS_FLAG,B.DESCRIPTION,B.ASSEMBLY_ITEM_ID,B.COMPONENT_CODE, ' ||
				'		B.EFFECTIVITY_DATE,B.DISABLE_DATE,' || v_language || ' ' ||
				'		DECODE (M.BOM_ITEM_TYPE || M.PICK_COMPONENTS_FLAG, ''1Y'',''P'',''1N'',''A'','''')  ' ||
                                '    AS MODEL_TYPE, ' ||
						v_trackable_flag || ', ' || v_config_model_type ||
                                    ', 702 AS BOM_APPLICATION_ID, 401 AS INV_APPLICATION_ID, ' ||
                                '    DECODE (M.IB_ITEM_INSTANCE_CLASS,''LINK'',''1'',''0'') AS IB_LINK_ITEM_FLAG, ' ||
                                '    B.shippable_item_flag,  ' ||
                                '    M.mtl_transactions_enabled_flag,  ' ||
                                '    B.replenish_to_order_flag,  ' ||
                                '    M.serial_number_control_code  ' ||
				'	FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B,  ' ||
                              '     MTL_SYSTEM_ITEMS_KFV'|| v_fndnam_link_name || ' M, ' ||
                              '     BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM '||v_mtl_system_items_tl||
                              ' WHERE M.INVENTORY_ITEM_ID(+) = B.COMPONENT_ITEM_ID ' ||
                              '    AND M.ORGANIZATION_ID(+) = B.ORGANIZATION_ID ' ||
                              '    AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID       ' ||
                              '    AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
                              '    AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL '||v_where_tl||v_rownum;

Line: 1726

				'SELECT M.INVENTORY_ITEM_ID, M.ORGANIZATION_ID, M.SEGMENT1, ' ||
 					'M.FIXED_LEAD_TIME, M.START_DATE_ACTIVE, M.END_DATE_ACTIVE, ' ||
 					'M.CUSTOMER_ORDER_ENABLED_FLAG,' || v_versioned_string ||', M.CONCATENATED_SEGMENTS, ' ||
 					'M.INVENTORY_ITEM_STATUS_CODE, M.ITEM_CATALOG_GROUP_ID, ' ||
 					'M.PRIMARY_UOM_CODE, M.BOM_ITEM_TYPE, ' ||v_description_tl || v_desc_tl ||
                                        ' B.TOP_ITEM_ID, B.EXPLOSION_TYPE, 401 AS INV_APPLICATION_ID  ' ||
				'FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
                                        'MTL_SYSTEM_ITEMS_KFV'|| v_fndnam_link_name || ' M ' ||
                                        ',BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM ' ||
                                        localTable || v_mtl_system_items_tl ||
                                ' WHERE M.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
                                'AND B.top_item_id = BBM.assembly_item_id AND b.organization_id = BBM.organization_id ' ||
                                'AND bbm.alternate_bom_designator IS NULL AND b.top_bill_sequence_id = bbm.bill_sequence_id ' ||
                                'AND M.ORGANIZATION_ID = B.ORGANIZATION_ID' || v_where_tl || v_where_lang_tl || v_rownum;

Line: 1745

					SELECT ADDRESS_ID,CUSTOMER_ID,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,COUNTRY,
					CITY,POSTAL_CODE,STATE,PROVINCE,COUNTY,BILL_TO_FLAG,SHIP_TO_FLAG
					FROM RA_ADDRESSES_ALL'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;

Line: 1751

					SELECT ADDRESS_ID,SITE_USE_CODE,WAREHOUSE_ID,SITE_USE_ID
					FROM RA_SITE_USES_ALL'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;

Line: 1756

					SELECT CUSTOMER_NAME,CUSTOMER_ID,PRICE_LIST_ID,CUSTOMER_CATEGORY_CODE,
					WAREHOUSE_ID,PRIMARY_SALESREP_ID
					FROM RA_CUSTOMERS'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;

Line: 1762

					SELECT C.CUSTOMER_ID,C.ADDRESS_ID,C.SALUTATION,C.LAST_NAME,C.FIRST_NAME,C.SUFFIX,
 					C.TITLE,C.PRIMARY_ROLE,C.EMAIL_ADDRESS,C.CONTACT_ID,P.PHONE_NUMBER PHONE,
 					R.PHONE_NUMBER FAX
					FROM RA_CONTACTS'|| v_fndnam_link_name || ' C,RA_PHONES'|| v_fndnam_link_name || ' P,
						RA_PHONES'|| v_fndnam_link_name || ' R
					WHERE C.CUSTOMER_ID=P.CUSTOMER_ID (+)
  						AND C.ADDRESS_ID=P.ADDRESS_ID   (+)
  						AND C.CONTACT_ID=P.CONTACT_ID   (+)
  						AND R.CUSTOMER_ID (+) =P.CUSTOMER_ID
  						AND R.ADDRESS_ID  (+) =P.ADDRESS_ID
  						AND R.CONTACT_ID  (+) =P.CONTACT_ID
  						AND R.PHONE_TYPE(+)=''FAX'' AND P.PHONE_TYPE (+)=''GEN''
  						AND C.STATUS=''A'''||v_rownum;

Line: 1778

					SELECT  L.NAME,L.DESCRIPTION,L.CURRENCY_CODE,L.PRICE_LIST_ID
					FROM CZ_XFR_PRICE_LISTS P,SO_PRICE_LISTS'|| v_fndnam_link_name || ' L
					WHERE P.PRICE_LIST_ID=L.PRICE_LIST_ID
  						AND P.DELETED_FLAG=''0'''||v_rownum;

Line: 1785

						SELECT M.INVENTORY_ITEM_ID,M.ORGANIZATION_ID,
       					L.LIST_PRICE,L.PRICE_LIST_ID,L.PRICE_LIST_LINE_ID
						FROM CZ_ITEM_MASTERS I,MTL_SYSTEM_ITEMS'|| v_fndnam_link_name || ' M,
							SO_PRICE_LIST_LINES'|| v_fndnam_link_name || ' L
						WHERE M.INVENTORY_ITEM_ID=L.INVENTORY_ITEM_ID
							AND to_char(M.ORGANIZATION_ID)=substr(I.ORIG_SYS_REF,instr(I.ORIG_SYS_REF,'':'',-1,1)+1)
							AND to_char(M.INVENTORY_ITEM_ID)=substr(I.ORIG_SYS_REF,1,instr(I.ORIG_SYS_REF,'':'',1,1)-1)
							AND I.DELETED_FLAG=''0'''||v_rownum;

Line: 1796

					SELECT SALESREP_ID,ORG_ID,EMAIL_ADDRESS,NAME
					FROM RA_SALESREPS_ALL'|| v_fndnam_link_name || '
					WHERE STATUS=''A'''||v_rownum;

Line: 1804

							SELECT M.ELEMENT_NAME,M.ELEMENT_VALUE,M.INVENTORY_ITEM_ID,
							I.ORGANIZATION_ID,I.TOP_ITEM_ID,I.EXPLOSION_TYPE
							FROM CZ_EXV_ITEM_MASTER I,MTL_DESCR_ELEMENT_VALUES'|| v_fndnam_link_name || ' M
							WHERE M.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID'||v_rownum;

Line: 1812

                                         ' SELECT M.ELEMENT_NAME, M.ELEMENT_VALUE, M.INVENTORY_ITEM_ID, MTL.ORGANIZATION_ID ' ||
                                         '   FROM MTL_SYSTEM_ITEMS_KFV' || v_fndnam_link_name || ' MTL, ' ||
                                         '        MTL_DESCR_ELEMENT_VALUES' || v_fndnam_link_name || ' M ' ||
                                         '  WHERE M.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID' || v_rownum;

Line: 1819

						'SELECT ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,DESCRIPTION ' ||
						'FROM MTL_DESCRIPTIVE_ELEMENTS'|| v_fndnam_link_name||v_where;

Line: 1824

					'SELECT ITEM_CATALOG_GROUP_ID,PARENT_CATALOG_GROUP_ID,DESCRIPTION,CATALOG_CONCAT_SEGS  ' ||
					'FROM MTL_ITEM_CATALOG_GROUPS_V'|| v_fndnam_link_name||v_where;

Line: 1829

                        ' SELECT element_name, element_value ' ||
                        '   FROM mtl_descr_element_values' || v_fndnam_link_name ||' WHERE element_value IS NOT NULL' || v_rownum;

Line: 1834

			 SELECT organization_id, top_item_id, explosion_type, rexplode_flag
			   FROM bom_explosions' || v_fndnam_link_name || v_where;

Line: 1841

        'SELECT attrgrps.attr_group_id,assocs.classification_code AS item_catalog_group_id,attrs.* '||
        'FROM EGO_ATTR_GROUPS_V'||v_fndnam_link_name||' attrgrps '||
        ',EGO_ATTRS_V'||v_fndnam_link_name||'  attrs '||
        ',EGO_OBJ_AG_ASSOCS_B'||v_fndnam_link_name||'  assocs '||
        ',EGO_ITMATTR_APPL_USGS_B'||v_fndnam_link_name||'  usgs '||
        ',FND_OBJECTS'||v_fndnam_link_name||' objs '||
        'WHERE objs.OBJ_NAME = ''EGO_ITEM'' '||
        'AND objs.OBJECT_ID = assocs.OBJECT_ID '||
        'AND assocs.ATTR_GROUP_ID = attrgrps.ATTR_GROUP_ID '||
        'AND assocs.ATTR_GROUP_ID = usgs.ATTR_GROUP_ID '|| --Bug14633568. Added additional clause
        'AND attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
        'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
        'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
        'AND usgs.ATTR_ID = attrs.ATTR_ID '||
        'AND usgs.ENABLED_FLAG = ''Y'' '||
        'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
        ' assocs.DATA_LEVEL=''ITEM_LEVEL'' AND attrs.ENABLED_FLAG=''Y'' '|| v_rownum;

Line: 1862

         ' SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL'|| v_fndnam_link_name||' c '||
         ' WHERE EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES a WHERE a.attr_group_id=c.attr_group_id) AND '||
         ' EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES b WHERE b.item_catalog_group_id=c.item_catalog_group_id)' || v_rownum;

Line: 1867

        'SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL '|| v_fndnam_link_name||' c '||
        ' WHERE EXISTS '||
        '(SELECT NULL FROM '||
        'EGO_ATTR_GROUPS_V '||v_fndnam_link_name||' attrgrps '||
        ',EGO_ATTRS_V '||v_fndnam_link_name||' attrs '||
        ',EGO_ITMATTR_APPL_USGS_B '||v_fndnam_link_name||' usgs '||
        ',FND_OBJECTS '||v_fndnam_link_name||' objs '||
        'WHERE attrgrps.attr_group_id=c.attr_group_id  AND objs.OBJ_NAME = ''EGO_ITEM'' '||
        'AND usgs.attr_group_id=c.attr_group_id  '|| --Bug14633568. Added additional clause
        'AND attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
        'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
        'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
        'AND usgs.ATTR_ID = attrs.ATTR_ID '||
        'AND usgs.ENABLED_FLAG = ''Y'' '||
        'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
        ' attrs.ENABLED_FLAG=''Y'') '||v_rownum;

Line: 1894

             ' SELECT  ' ||
             ' -1   AS ATTR_GROUP_ID ' ||
             ' ,-1  AS ITEM_CATALOG_GROUP_ID ' ||
             ' ,-1  AS ATTR_ID               ' ||
             ' ,-1  AS APPLICATION_ID        ' ||
             ' ,-1  AS ATTR_GROUP_TYPE       ' ||
             ' ,''*'' AS ATTR_GROUP_NAME     ' ||
             ' ,''*'' AS ATTR_NAME           ' ||
             ' ,''*'' AS ATTR_DISPLAY_NAME   ' ||
             ' ,''*'' AS DESCRIPTION         ' ||
             ' ,''*'' AS DATABASE_COLUMN     ' ||
             ' ,''*'' AS DATA_TYPE_CODE      ' ||
             ' ,-1  AS SEQUENCE              ' ||
             ' ,''*'' AS UNIQUE_KEY_FLAG     ' ||
             ' ,''*'' AS DEFAULT_VALUE       ' ||
             ' ,''*'' AS INFO_1              ' ||
             ' ,''*'' AS UOM_CLASS           ' ||
             ' ,-1  AS CONTROL_LEVEL         ' ||
             ' ,-1  AS VALUE_SET_ID          ' ||
             ' ,''*'' AS VALUE_SET_NAME      ' ||
             ' ,''*'' AS FORMAT_CODE         ' ||
             ' ,-1  AS MAXIMUM_SIZE          ' ||
             ' ,''*'' AS VALIDATION_CODE     ' ||
             ' ,''*'' AS LONGLIST_FLAG       ' ||
             ' ,''*'' AS ENABLED_FLAG        ' ||
             ' ,''*'' AS ENABLED_MEANING     ' ||
             ' ,''*'' AS REQUIRED_FLAG       ' ||
             ' ,''*'' AS REQUIRED_MEANING    ' ||
             ' ,''*'' AS SEARCH_FLAG         ' ||
             ' ,''*'' AS SEARCH_MEANING      ' ||
             ' ,''*'' AS DISPLAY_CODE        ' ||
             ' ,''*'' AS DISPLAY_MEANING     ' ||
             ' ,''*'' AS ATTRIBUTE_CODE      ' ||
             ' ,''*'' AS VIEW_IN_HIERARCHY_CODE ' ||
             ' ,''*'' AS EDIT_IN_HIERARCHY_CODE ' ||
             ' ,''*'' AS CUSTOMIZATION_LEVEL  ' ||
             ' FROM dual';

Line: 1935

             'SELECT  ' ||
             '-1   AS EXTENSION_ID                 ' ||
             ',-1  AS ORGANIZATION_ID              ' ||
             ',-1  AS INVENTORY_ITEM_ID            ' ||
             ',-1  AS REVISION_ID                  ' ||
             ',-1  AS ITEM_CATALOG_GROUP_ID        ' ||
             ',-1  AS ATTR_GROUP_ID                ' ||
             ',''*'' AS SOURCE_LANG                ' ||
             ',''*'' AS LANGUAGE                   ' ||
             'FROM dual';

Line: 1970

        SELECT local_name,fndnam_link_name,import_enabled
        INTO v_local_name,p_db_link,v_import_enabled FROM CZ_SERVERS
        WHERE import_enabled='1';

Line: 2193

		SELECT count(*)
		INTO   v_server_count
		FROM   cz_servers
		WHERE   cz_servers.local_name = v_local_name
		OR (    cz_servers.hostname = v_hostname
		AND	 cz_servers.instance_name = v_instance_name
		AND	 cz_servers.db_listener_port = v_db_listener_port) ;

Line: 2208

		SELECT decode(import_enabled,'Y','1','N','0','0')
		INTO v_import_enabled
		FROM dual;

Line: 2213

			SELECT count(*) INTO v_import_count FROM cz_servers
			WHERE import_enabled = '1';

Line: 2248

			SELECT cz_servers_s.NEXTVAL
			INTO	 v_server_id
			FROM   dual;

Line: 2251

			FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting ' || v_server_id);

Line: 2252

			INSERT INTO CZ_SERVERS (SERVER_LOCAL_ID,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
					  		INSTANCE_NAME, FNDNAM, GLOBAL_IDENTITY, NOTES,
					  		FNDNAM_LINK_NAME, IMPORT_ENABLED)
					VALUES (v_server_id,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
					  		INSTANCE_NAME, FNDNAM, V_GLOBAL_IDENTITY, NOTES,
					  		FNDNAM_LINK_NAME, v_IMPORT_ENABLED );

Line: 2261

		      errbuf := cz_utils.get_text('CZ_CANNOT_INSERT_SERVER','SQLERRM',Sqlerrm);

Line: 2343

		SELECT SERVER_LOCAL_ID,hostname,db_listener_port,instance_name,
			fndnam,global_identity,notes,upper(fndnam_link_name),import_enabled
		INTO  v_server_id,l_hostname,l_db_listener_port,l_instance_name,
			l_fndnam,l_global_identity,l_notes,l_fndnam_link_name,l_import_enabled
		FROM   cz_servers
		WHERE  cz_servers.local_name = v_local_name;

Line: 2352

				SELECT decode(import_enabled,'Y','1','N','0','0')
				INTO v_import_enabled
				FROM dual;

Line: 2356

				SELECT local_name into matching_local_name
				FROM cz_servers
				WHERE hostname = v_hostname
				AND	instance_name = v_instance_name
				AND	db_listener_port = v_db_listener_port
				AND	fndnam = v_fndnam
				AND	local_name <> v_local_name;

Line: 2388

				SELECT count(*) INTO v_import_count FROM cz_servers
				WHERE import_enabled = '1'
				AND	local_name <> v_local_name;

Line: 2471

			UPDATE CZ_SERVERS	SET
				LOCAL_NAME	= v_local_name,
				HOSTNAME = v_hostname,
				DB_LISTENER_PORT = v_db_listener_port,
				INSTANCE_NAME = v_instance_name,
      			FNDNAM 	       = v_fndnam		      ,
				GLOBAL_IDENTITY   = v_global_identity    ,
				NOTES		 = v_notes		      ,
	      		FNDNAM_LINK_NAME  = v_fndnam_link_name   ,
				IMPORT_ENABLED    = v_import_enabled
			WHERE  cz_servers.server_local_id = v_server_id ;

Line: 2537

SELECT LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT, INSTANCE_NAME,
SERVER_DB_VERSION, FNDNAM, GLOBAL_IDENTITY, NOTES,FNDNAM_LINK_NAME, Decode(IMPORT_ENABLED, '1', 'Y', '0','N',IMPORT_ENABLED)
FROM CZ_SERVERS
WHERE SERVER_LOCAL_ID >= 0;

Line: 2599

                    'SELECT * FROM cz_repository_main_hgrid_v@' || v_link_name ||
                    ' WHERE object_type = ''FLD''';

Line: 2640

		SELECT hostname,
			 instance_name,
			 db_listener_port,
			 fndnam,
		 	 fndnam_link_name,
			 import_enabled,
                   server_local_id
		  INTO v_hostname,
			 v_instance_name,
			 v_db_listener_port,
			 v_fndnam,
			 v_fndnam_link_name,
			 v_import_enabled,
                   v_server_id
		 FROM  cz_servers
		 WHERE cz_servers.local_name = v_local_name;

Line: 2716

		  		v_newString     := 'SELECT version INTO  :v_server_db_version  FROM V$INSTANCE@' || v_fndnam_link_name ;

Line: 2736

				UPDATE CZ_SERVERS	SET
					SERVER_DB_VERSION = v_server_db_version
				WHERE  cz_servers.local_name = v_local_name ;

Line: 2742

			      errbuf := cz_utils.get_text('CZ_DB_VERSION_UPDATE_FAILURE', 'SQLERRM', Sqlerrm);

Line: 3097

	SELECT rtrim(substr(orig_sys_ref,instr(orig_sys_ref,':',1,1)+1,length(substr(orig_sys_ref,instr(orig_sys_ref,':',1,1)+1)) -
							length(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2)))  )) ,
		rtrim(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2)+1))
	INTO lOrg_Id, lTop_Id
	FROM cz_devl_projects
	WHERE devl_project_id = iModel_Id
	AND	deleted_flag = '0';

Line: 3105

	select decode(copy_child_models,'Y',0,'N',1,0) into lCopy_Child_Models from dual;

Line: 3113

        SELECT local_name,fndnam_link_name,import_enabled
        INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
        WHERE import_enabled='1';

Line: 3189

 UPDATE cz_xfr_project_bills
	SET  deleted_flag=Decode(iImportEnabled, 'Y', '0', 'N', '1', iImportEnabled)
	WHERE model_ps_node_id = iModel_Id;

Line: 3219

      SELECT local_name,fndnam_link_name,import_enabled
      INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
      WHERE import_enabled='1';

Line: 3291

      SELECT local_name,fndnam_link_name,import_enabled
      INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
      WHERE import_enabled='1';

Line: 3369

      SELECT local_name,fndnam_link_name,import_enabled
      INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
      WHERE import_enabled='1';

Line: 3479

    SELECT LOCAL_NAME
    INTO v_TARGET_INSTANCE
    FROM CZ_SERVERS
    WHERE import_enabled='1';

Line: 3521

       FOR i IN(SELECT message FROM CZ_DB_LOGS
                WHERE run_id=v_err ORDER BY logtime)
       LOOP
          errbuf:=i.message;

Line: 3538

PROCEDURE UpdateImportFlag_cp
(errbuf         OUT NOCOPY VARCHAR2,
 retcode        OUT NOCOPY NUMBER,
 iFolder_ID     IN  NUMBER,
 iModel_Id      IN  VARCHAR2,
 iImportEnabled IN  VARCHAR2) IS

 retStatus VARCHAR2(3);

Line: 3560

   log_report('UpdateImportFlag_cp: ' || errbuf);

Line: 3567

     log_report('UpdateImportFlag_cp: ' || errbuf);

Line: 3571

     log_report('UpdateImportFlag_cp: ' || errbuf);