The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(nSURR_KEY,((INSTR(nSURR_KEY,':')+1)))
INTO nORG_ID
FROM DUAL;
SELECT RTRIM(nSURR_KEY,nORG_ID)
INTO nITEM_ID
FROM DUAL;
SELECT RTRIM(nITEM_ID,':')
INTO nITEM_ID
FROM DUAL;
PROCEDURE delete_spx_process
(p_short_name IN VARCHAR2,
p_application IN VARCHAR2) AS
BEGIN
fnd_program.delete_program(p_short_name,p_application);
fnd_program.delete_executable(p_short_name,p_application);
'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);
LOG_REPORT('Error : < SellingPoint Order Export Update Status Process > REGISTRATION');
'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);
LOG_REPORT('Error : < SellingPoint Customer Export Update Status Process > REGISTRATION');
default_value=>'select :$FLEX$.CZ_ORG_ID from dual',
display=>'N',
display_size=>10,
description_size=>10,
concatenated_description_size=>50,
prompt=>'dsORG_ID');
PROCEDURE delete_bom_sync_process
(application_name IN VARCHAR2 -- default 'Oracle Configurator'
) AS
BEGIN
BEGIN
fnd_program.delete_program('CZBOMSIM',application_name);
fnd_program.delete_executable('CZBOMSIM',application_name);
fnd_program.delete_program('CZALLBOMSIM',application_name);
fnd_program.delete_executable('CZALLBOMSIM',application_name);
fnd_program.delete_program('CZALLBOMSYNC',application_name);
fnd_program.delete_executable('CZALLBOMSYNC',application_name);
register_spx_process('Select Tables to Be Imported',
'CZREPSIMP',
application_name,
'Select Tables to Be Imported',
'CZ_ORAAPPS_INTEGRATE.SETTABLEIMPORT',
request_group,
cz_schema);
LOG_REPORT('Error : < Select Tables to Be Imported > REGISTRATION');
PROCEDURE delete_service_process
(application_name IN VARCHAR2 -- default 'System Administration'
) AS
BEGIN
BEGIN
fnd_program.delete_program('SPREPGS',application_name);
fnd_program.delete_executable('SPREPGS',application_name);
fnd_program.delete_program('SPREPIN',application_name);
fnd_program.delete_executable('SPREPIN',application_name);
fnd_program.delete_program('SPREPGIMP',application_name);
fnd_program.delete_executable('SPREPGIMP',application_name);
fnd_program.delete_program('SPREPSIMP',application_name);
fnd_program.delete_executable('SPREPSIMP',application_name);
fnd_program.delete_program('SPCREATELNK',application_name);
fnd_program.delete_executable('SPCREATELNK',application_name);
fnd_program.delete_program('SPPUBLISHMODEL',application_name);
fnd_program.delete_executable('SPPUBLISHMODEL',application_name);
fnd_program.delete_program('SPPOPULATESRV',application_name);
fnd_program.delete_executable('SPPOPULATESRV',application_name);
PROCEDURE delete_export_process
(application_name IN VARCHAR2 -- default 'Oracle Order Entry'
) AS
ar_application_name VARCHAR2(50):='Oracle Receivables';
fnd_program.delete_program('SPOEEXP',application_name);
fnd_program.delete_executable('SPOEEXP',application_name);
fnd_program.delete_program('SPOEEXPUS',application_name);
fnd_program.delete_executable('SPOEEXPUS',application_name);
fnd_program.delete_program('SPAREXP',ar_application_name);
fnd_program.delete_executable('SPAREXP',ar_application_name);
fnd_program.delete_program('SPAREXPUS',ar_application_name);
fnd_program.delete_executable('SPAREXPUS',ar_application_name);
fnd_program.delete_program('SPAREXPSC',ar_application_name);
fnd_program.delete_executable('SPAREXPSC',ar_application_name);
fnd_program.delete_program('SPOEEXPSO',application_name);
fnd_program.delete_executable('SPOEEXPSO',application_name);
PROCEDURE delete_import_process
(application_name IN VARCHAR2 -- default 'Oracle Bills of Material'
) AS
BEGIN
BEGIN
fnd_program.delete_program('SPAPPIMP','System Administration');
fnd_program.delete_executable('SPAPPIMP','System Administrationdel');
fnd_program.delete_program('SPAPPIMPPCM',application_name);
fnd_program.delete_executable('SPAPPIMPPCM',application_name);
fnd_program.delete_program('SPAPPIMPRFCM',application_name);
fnd_program.delete_executable('SPAPPIMPRFCM',application_name);
fnd_program.delete_program('SPAPPIMPRMCM',application_name);
fnd_program.delete_executable('SPAPPIMPRMCM',application_name);
stmt:='SELECT setting_id,value from '||var_schema||'CZ_DB_SETTINGS WHERE section_name like '''||section_name||
''' and setting_id like '''||setting_id||''' ';
SELECT SECTION_NAME,SETTING_ID,VALUE,DESC_TEXT FROM CZ_DB_SETTINGS
WHERE SECTION_NAME LIKE LIKE_SectionName AND SETTING_ID LIKE LIKE_SettingID;
SELECT 'X' FROM cz_db_settings
WHERE section_name=section_Name AND
setting_id=sSetting_Id;
UPDATE cz_db_settings SET value=sValue WHERE section_name=sSection_Name AND setting_id=sSetting_Id;
INSERT INTO cz_db_settings(setting_id,section_name,data_type,value,desc_text)
VALUES(sSetting_Id,sSection_Name,sType,sValue,sDESCRIPTION);
SELECT DST_TABLE,XFR_GROUP,DISABLED FROM CZ_XFR_TABLES
WHERE DST_TABLE LIKE LIKE_DstTableName AND XFR_GROUP LIKE LIKE_PhaseName;
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;
EXECUTE IMMEDIATE 'SELECT NULL FROM DUAL@' || p_link_name;
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) || '.%';
EXECUTE IMMEDIATE 'SELECT sysdate FROM dual'||v_db_link
INTO v_temp;
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';
SELECT fndnam_link_name INTO v_fndnam_link_name
FROM cz_servers
WHERE local_name = slocal_name;
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';
' 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;
SELECT ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS'|| v_fndnam_link_name;*/
'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;
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''';
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''';
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'' ';
'SELECT distinct T.DESCRIPTION, T.LANGUAGE, T.SOURCE_LANG, ' ||
'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
'P.TOP_ITEM_ID, P.EXPLOSION_TYPE, P.ORGANIZATION_ID, ' ||
'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
'FROM CZ_XFR_PROJECT_BILLS P, ' ||
'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 P.ORGANIZATION_ID=BBM.ORGANIZATION_ID ' ||
'AND P.TOP_ITEM_ID=BBM.ASSEMBLY_ITEM_ID ' ||
'AND P.EXPLOSION_TYPE=B.EXPLOSION_TYPE ' ||
'AND T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
'AND T.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 ' ||
'AND P.DELETED_FLAG=''0'' ' ||
'AND T.LANGUAGE IN (SELECT language_code ' ||
' FROM fnd_languages' || v_fndnam_link_name ||
' WHERE installed_flag IN (''B'', ''I''))';
'SELECT distinct T.DESCRIPTION, F.LANGUAGE_CODE, F.LANGUAGE_CODE AS SOURCE_LANG, ' ||
'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
'P.TOP_ITEM_ID, P.EXPLOSION_TYPE, P.ORGANIZATION_ID, ' ||
'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
'FROM CZ_XFR_PROJECT_BILLS P, ' ||
'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 P.ORGANIZATION_ID=BBM.ORGANIZATION_ID ' ||
'AND P.TOP_ITEM_ID=BBM.ASSEMBLY_ITEM_ID ' ||
'AND P.EXPLOSION_TYPE=B.EXPLOSION_TYPE ' ||
'AND M.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
'AND T.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 ' ||
'AND P.DELETED_FLAG=''0'' ' ||
'AND F.INSTALLED_FLAG in (''B'', ''I''))';
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''';
' 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;
'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;
'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;
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;
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;
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;
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;
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;
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;
SELECT SALESREP_ID,ORG_ID,EMAIL_ADDRESS,NAME
FROM RA_SALESREPS_ALL'|| v_fndnam_link_name || '
WHERE STATUS=''A'''||v_rownum;
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;
' 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;
'SELECT ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,DESCRIPTION ' ||
'FROM MTL_DESCRIPTIVE_ELEMENTS'|| v_fndnam_link_name||v_where;
'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;
' SELECT element_name, element_value ' ||
' FROM mtl_descr_element_values' || v_fndnam_link_name ||' WHERE element_value IS NOT NULL' || v_rownum;
SELECT organization_id, top_item_id, explosion_type, rexplode_flag
FROM bom_explosions' || v_fndnam_link_name || v_where;
'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 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;
' 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;
'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 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;
' 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';
'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';
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';
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) ;
SELECT decode(import_enabled,'Y','1','N','0','0')
INTO v_import_enabled
FROM dual;
SELECT count(*) INTO v_import_count FROM cz_servers
WHERE import_enabled = '1';
SELECT cz_servers_s.NEXTVAL
INTO v_server_id
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting ' || v_server_id);
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 );
errbuf := cz_utils.get_text('CZ_CANNOT_INSERT_SERVER','SQLERRM',Sqlerrm);
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;
SELECT decode(import_enabled,'Y','1','N','0','0')
INTO v_import_enabled
FROM dual;
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;
SELECT count(*) INTO v_import_count FROM cz_servers
WHERE import_enabled = '1'
AND local_name <> v_local_name;
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 ;
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;
'SELECT * FROM cz_repository_main_hgrid_v@' || v_link_name ||
' WHERE object_type = ''FLD''';
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;
v_newString := 'SELECT version INTO :v_server_db_version FROM V$INSTANCE@' || v_fndnam_link_name ;
UPDATE CZ_SERVERS SET
SERVER_DB_VERSION = v_server_db_version
WHERE cz_servers.local_name = v_local_name ;
errbuf := cz_utils.get_text('CZ_DB_VERSION_UPDATE_FAILURE', 'SQLERRM', Sqlerrm);
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';
select decode(copy_child_models,'Y',0,'N',1,0) into lCopy_Child_Models from dual;
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';
UPDATE cz_xfr_project_bills
SET deleted_flag=Decode(iImportEnabled, 'Y', '0', 'N', '1', iImportEnabled)
WHERE model_ps_node_id = iModel_Id;
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';
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';
SELECT LOCAL_NAME
INTO v_TARGET_INSTANCE
FROM CZ_SERVERS
WHERE import_enabled='1';
FOR i IN(SELECT message FROM CZ_DB_LOGS
WHERE run_id=v_err ORDER BY logtime)
LOOP
errbuf:=i.message;