[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
CURSOR c_imp_intl_text IS
SELECT DISTINCT orig_sys_ref, fsk_devlproject_1_1
FROM CZ_IMP_LOCALIZED_TEXTS
WHERE rec_status IS NULL AND Run_ID = inRUN_ID
ORDER BY orig_sys_ref;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
SELECT ol.intl_text_id,ol.localized_str, ol.model_id, ol.orig_sys_ref,ol.language
FROM cz_localized_texts ol, cz_devl_projects od, cz_ps_nodes op,
cz_imp_ps_nodes ip, cz_imp_devl_project id
WHERE ol.orig_sys_ref = sImpOrigSysRef(i)
AND ip.fsk_intltext_1_1 = sImpOrigSysRef(i)
AND op.intl_text_id = ol.intl_text_id
AND ip.orig_sys_ref = op.orig_sys_ref
AND ol.model_id = od.devl_project_id
AND id.devl_project_id = od.devl_project_id
AND id.orig_sys_ref = sImpFSKDevlProject(i)
AND op.devl_project_id = ol.model_id
AND ip.fsk_devlproject_5_1 = od.orig_sys_ref
AND ol.deleted_flag = '0'
AND op.deleted_flag = '0'
AND od.deleted_flag = '0'
AND ip.run_id = inRUN_ID
AND id.run_id = inRUN_ID
AND id.rec_status='OK';
SELECT localized_str
FROM CZ_IMP_LOCALIZED_TEXTS
WHERE Run_ID = inRUN_ID
AND fsk_devlproject_1_1=sImpFSKDevlProject(i)
AND orig_sys_ref=sImpOrigSysRef(i)
AND language = nOnlLang;
nUpdateCount:=nUpdateCount+1;
UPDATE cz_imp_localized_texts
SET disposition = l_disposition,
rec_status = 'PASS',
intl_text_id = nOnlIntlTextId,
model_id = nOnlModelId
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND run_id = inRUN_ID
AND fsk_devlproject_1_1=sImpFSKDevlProject(i)
AND language=nOnlLang;
UPDATE CZ_IMP_PS_NODES
SET INTL_TEXT_ID =nOnlIntlTextId
WHERE fsk_intltext_1_1 = sImpOrigSysRef(i)
AND fsk_devlproject_5_1 = sImpFSKDevlProject(i)
AND RUN_ID = inRUN_ID;
ELSE /* insert */
BEGIN
SELECT devl_project_id INTO nModelId
FROM cz_imp_devl_project
WHERE orig_sys_ref = sImpFSKDevlProject(i)
AND run_id = inRUN_ID
AND rec_status = 'OK';
SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE cz_imp_localized_texts
SET intl_text_id = nNextId,
model_id = nModelId,
disposition = 'I',
rec_status = 'PASS'
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND fsk_devlproject_1_1 = sImpFSKDevlProject(i)
AND run_id = inRUN_ID;
UPDATE CZ_IMP_PS_NODES
SET INTL_TEXT_ID =nNextId
WHERE fsk_intltext_1_1 = sImpOrigSysRef(i)
AND fsk_devlproject_5_1 = sImpFSKDevlProject(i)
AND RUN_ID = inRUN_ID;
nInsertCount:=nInsertCount+1;
UPDATE cz_imp_localized_texts
SET disposition='R',
rec_status='FAIL'
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND fsk_devlproject_1_1 = sImpFSKDevlProject(i)
AND run_id = inRUN_ID;
SELECT COUNT(*) INTO var_tl_prop FROM cz_imp_item_property_value
WHERE run_id = inRUN_ID
AND FSK_LOCALIZEDTEXT_3_1 IS NOT NULL
AND FSK_LOCALIZEDTEXT_3_1=sImpOrigSysRef(i) AND rownum<2;
SELECT ol.intl_text_id,ol.localized_str, ol.orig_sys_ref,ol.language
FROM cz_localized_texts ol
WHERE ol.orig_sys_ref = sImpOrigSysRef(i)
AND EXISTS(SELECT NULL FROM cz_imp_item_property_value
WHERE run_id = inRUN_ID
AND FSK_LOCALIZEDTEXT_3_1 IS NOT NULL
AND FSK_LOCALIZEDTEXT_3_1=ol.ORIG_SYS_REF)
AND ol.deleted_flag = '0';
SELECT localized_str
FROM CZ_IMP_LOCALIZED_TEXTS
WHERE Run_ID = inRUN_ID
AND orig_sys_ref=sImpOrigSysRef(i)
AND language = nOnlLangItem;
nUpdateCount:=nUpdateCount+1;
UPDATE cz_imp_localized_texts
SET disposition = l_disposition2,
rec_status = 'PASS',
intl_text_id = nOnlIntlTextId,
model_id = 0
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND run_id = inRUN_ID
AND language=nOnlLangItem;
ELSE /* insert */
BEGIN --#####
SELECT COUNT(*) INTO var_tl_prop FROM cz_imp_item_property_value
WHERE run_id = inRUN_ID
AND FSK_LOCALIZEDTEXT_3_1 IS NOT NULL
AND FSK_LOCALIZEDTEXT_3_1=sImpOrigSysRef(i)
AND rownum<2;
SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE cz_imp_localized_texts
SET intl_text_id = nNextId,
model_id = 0,
disposition = 'I',
rec_status = 'PASS'
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND run_id = inRUN_ID;
nInsertCount:=nInsertCount+1;
UPDATE cz_imp_localized_texts
SET disposition='R',
rec_status='FAIL'
WHERE orig_sys_ref = sImpOrigSysRef(i)
AND run_id = inRUN_ID;
sImpOrigSysRef.DELETE;
sImpFSKDevlProject.DELETE;
sImpLanguage.DELETE;
sImpSourceLang.DELETE;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
SELECT DISTINCT orig_sys_ref FROM cz_imp_localized_texts
WHERE rec_status IS NULL AND run_id = inRUN_ID AND orig_sys_ref IS NOT NULL
AND fsk_devlproject_1_1 IS NOT NULL AND language IS NOT NULL AND source_lang IS NOT NULL;
SELECT DISTINCT orig_sys_ref FROM cz_imp_localized_texts
WHERE rec_status IS NULL AND run_id = inRUN_ID
AND (orig_sys_ref IS NULL OR language IS NULL OR source_lang IS NULL);
l_orig_sys_ref_tbl.DELETE;
UPDATE cz_imp_localized_texts
SET deleted_flag = '0'
WHERE orig_sys_ref = l_orig_sys_ref_tbl(i)
AND run_id = inRUN_ID
AND deleted_flag IS NULL;
l_orig_sys_ref_tbl.DELETE;
UPDATE cz_imp_localized_texts
SET disposition = 'R',
rec_status = 'FAIL',
message = l_msg
WHERE orig_sys_ref = l_orig_sys_ref_tbl(i) AND run_id = inRUN_ID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /* COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /* Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /* Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
KRS_INTL_TEXT(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,NOCHANGE,inXFR_GROUP);
XFR_INTL_TEXT(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,nNoChangeCount,inXFR_GROUP);
/* Report Insert Errors */
IF(nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PS_NODE.MAIN_INTL_TEXT:INSERTS ',11276,inRun_Id);
/* Report Update Errors */
IF(nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PS_NODE.MAIN_INTL_TEXT:UPDATES ',11276,inRun_Id);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
CURSOR c_xfr_intl_text IS
SELECT intl_text_id, localized_str, language, source_lang, deleted_flag,creation_date
last_update_date, created_by, last_updated_by,orig_sys_ref, model_id, disposition,
rec_status, ROWID
FROM CZ_IMP_LOCALIZED_TEXTS
WHERE Run_ID=inRUN_ID AND rec_status = 'PASS';
nInsertCount PLS_INTEGER:=0; -- Inserts --
nUpdateCount PLS_INTEGER:=0; -- Updates --
NOUPDATE_ORIG_SYS_REF NUMBER;
NOUPDATE_LOCALIZED_STR NUMBER;
NOUPDATE_LANGUAGE NUMBER;
NOUPDATE_SOURCE_LANG NUMBER;
NOUPDATE_CREATION_DATE NUMBER;
NOUPDATE_LAST_UPDATE_DATE NUMBER;
NOUPDATE_CREATED_BY NUMBER;
NOUPDATE_LAST_UPDATED_BY NUMBER;
NOUPDATE_DELETED_FLAG NUMBER;
NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','ORIG_SYS_REF',inXFR_GROUP);
NOUPDATE_LOCALIZED_STR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LOCALIZED_STR',inXFR_GROUP);
NOUPDATE_LANGUAGE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LANGUAGE',inXFR_GROUP);
NOUPDATE_SOURCE_LANG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','SOURCE_LANG',inXFR_GROUP);
NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','CREATION_DATE',inXFR_GROUP);
NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LAST_UPDATE_DATE',inXFR_GROUP);
NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','CREATED_BY',inXFR_GROUP);
NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LAST_UPDATED_BY',inXFR_GROUP);
NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','DELETED_FLAG',inXFR_GROUP);
INSERT INTO cz_localized_texts (intl_text_id, localized_str, language, source_lang, deleted_flag,
creation_date, last_update_date, created_by, last_updated_by,orig_sys_ref, model_id)
VALUES
(p_xfr_intl_text.intl_text_id,
p_xfr_intl_text.localized_str,
p_xfr_intl_text.language,
p_xfr_intl_text.source_lang,
p_xfr_intl_text.deleted_flag,
sysdate, sysdate, -UID, -UID,p_xfr_intl_text.orig_sys_ref, p_xfr_intl_text.model_id);
nInsertCount:=nInsertCount+1;
UPDATE cz_imp_localized_texts
SET intl_text_id=p_xfr_intl_text.intl_text_id,
REC_STATUS='OK'
WHERE ROWID = p_xfr_intl_text.ROWID;
UPDATE cz_imp_localized_texts
SET DISPOSITION='R',
REC_STATUS='DUPL'
WHERE ROWID = p_xfr_intl_text.ROWID;
UPDATE cz_imp_localized_texts
SET REC_STATUS='ERR'
WHERE ROWID = p_xfr_intl_text.ROWID;
x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT:INSERT',11276,inRun_Id);
UPDATE cz_localized_texts SET
localized_str=DECODE(NOUPDATE_LOCALIZED_STR,0,p_xfr_intl_text.localized_str,localized_str),
deleted_flag=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_intl_text.deleted_flag,deleted_flag),
source_lang=DECODE(NOUPDATE_SOURCE_LANG,0,p_xfr_intl_text.source_lang,source_lang),
LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,sysdate,LAST_UPDATE_DATE),
LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY)
WHERE intl_text_id=p_xfr_intl_text.intl_text_id
AND model_id = p_xfr_intl_text.model_id
AND language = p_xfr_intl_text.language;
nUpdateCount:=nUpdateCount+1;
UPDATE cz_imp_localized_texts
SET REC_STATUS='OK'
WHERE ROWID = p_xfr_intl_text.ROWID;
UPDATE cz_imp_localized_texts
SET REC_STATUS='ERR'
WHERE ROWID = p_xfr_intl_text.ROWID;
x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT:UPDATE',11276,inRun_Id);
UPDATE cz_imp_localized_texts
SET REC_STATUS='OK'
WHERE ROWID = p_xfr_intl_text.ROWID;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM cz_imp_localized_texts
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2,
inCONFIG_ENGINE_TYPE IN VARCHAR2
) IS
CURSOR c_imp_devl_project IS
SELECT plan_level,orig_sys_ref,name,fsk_intltext_1_1,organization_id,top_item_id,explosion_type,model_id,model_type,
devl_project_id,intl_text_id,product_key,config_engine_type,desc_text,ROWID
FROM CZ_IMP_DEVL_PROJECT
WHERE rec_status IS NULL
AND Run_ID = inRUN_ID
ORDER BY model_id,plan_level,ORIG_SYS_REF,NAME,ROWID;
cDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
SELECT decode(upper(VALUE),'TRUE',1,'FALSE',0,'T',1,'F',0,'1',1,'0',0,'YES',1,'NO',0,'Y',1,'N',0,0)
INTO disableMultipleCopyImport FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
SELECT NVL(copy_addl_child_models,'0') INTO COPY_CHILD_MODELS
FROM cz_xfr_project_bills
WHERE model_ps_node_id = REFRESH_MODEL_ID;
nCurStmt := 'SELECT devl_project_id, persistent_project_id,intl_text_id,name,model_type,product_key,desc_text FROM cz_devl_projects ' --VR Added extra columns to check for update status
|| ' WHERE ORIG_SYS_REF= :1 '
|| ' AND DELETED_FLAG= ''0'' '
|| ' AND IMPORT_ENABLED = ''1'' '
|| ' AND NVL(config_engine_type,''L'') = :2' ; --LA 12548898 Engine Sync
SELECT COUNT(DISTINCT(config_engine_type)) INTO v_engine_cnt
FROM cz_devl_projects WHERE ORIG_SYS_REF=sOrigSysRef
AND deleted_flag = '0' ;
nCurStmt := 'SELECT devl_project_id,intl_text_id,name,model_type,product_key,desc_text FROM cz_devl_projects '
|| ' WHERE ORIG_SYS_REF= :1 '
|| ' AND DELETED_FLAG= ''0'' '
|| ' AND DEVL_PROJECT_ID=PERSISTENT_PROJECT_ID '
|| ' AND NVL(config_engine_type,''L'') = :2' ; --LA 12548898 Engine Sync
SELECT NULL FROM cz_devl_projects
WHERE devl_project_id = REFRESH_MODEL_ID
AND deleted_flag = '0';
SELECT d.devl_project_id, e.model_ref_expl_id FROM cz_devl_projects d, cz_model_ref_expls e
WHERE d.deleted_flag = '0'
AND e.deleted_flag = '0'
AND d.orig_sys_ref = sOrigSysRef
AND e.model_id = REFRESH_MODEL_ID
AND d.devl_project_id = e.component_id;
SELECT null FROM cz_rp_entries rp, cz_devl_projects dv
WHERE rp.deleted_flag = '0'
AND rp.object_type = 'PRJ'
AND rp.name = sName
AND dv.deleted_flag = '0'
AND dv.orig_sys_ref = sOrigSysRef
AND rp.object_id = dv.devl_project_id;
SELECT MAX(cz_utils.conv_num(SUBSTR(rp.name, 7, INSTR(rp.name, ')') - 7))) INTO nDummy
FROM cz_rp_entries rp, cz_devl_projects dv
WHERE rp.deleted_flag = '0'
AND rp.object_type = 'PRJ'
AND rp.name like 'Copy (%) of ' || sName
AND dv.deleted_flag = '0'
AND dv.orig_sys_ref = sOrigSysRef
AND rp.object_id = dv.devl_project_id;
SELECT name, model_type INTO onlName, sModelType
FROM cz_devl_projects WHERE devl_project_id = nOnlDevlProjectId;
nUpdateCount:=nUpdateCount+1;
-- Don't update model_type if it is 'P'
IF (sModelType = 'P' AND nModelType NOT IN ('P', 'N')) THEN
CZ_REFS.SolutionBasedModelCheck(nOnlDevlProjectId, nInstances);
/* Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_DEVL_PROJECT SET
DEVL_PROJECT_ID=DECODE(sDisposition,'R',DEVL_PROJECT_ID,nOnlDevlProjectId),
PERSISTENT_PROJECT_ID=DECODE(sDisposition,'I',NVL(nPersistentProjectId,nOnlDevlProjectId),PERSISTENT_PROJECT_ID),
INTL_TEXT_ID=DECODE(sDisposition,'R',INTL_TEXT_ID,nOnlIntlTextId),
NAME=DECODE(sDisposition,'R',NAME,sName),
DISPOSITION=sDisposition,
REC_STATUS=sRecStatus,
MODEL_TYPE = DECODE(sDisposition,'M',DECODE(sModelType,'P',DECODE(nModelType,'N',nModelType,sModelType),nModelType),nModelType)
WHERE ROWID = thisRowId;
UPDATE CZ_IMP_DEVL_PROJECT SET
DISPOSITION=sDisposition,
REC_STATUS=sRecStatus
WHERE MODEL_ID = REFRESH_MODEL_ID;
UPDATE CZ_IMP_PS_NODES SET
DEVL_PROJECT_ID=nOnlDevlProjectId
WHERE fsk_devlproject_5_1 = sOrigSysRef
AND RUN_ID=inRUN_ID;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
SELECT DELETED_FLAG, bom_caption_rule_id, nonbom_caption_rule_id,
orig_sys_ref, name, model_id, model_type, seeded_flag, ROWID FROM CZ_IMP_DEVL_PROJECT
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
SELECT 1 INTO l_nbr
FROM cz_rules a
WHERE rule_id = p_id
AND rule_type = p_type
AND deleted_flag = '0'
AND (devl_project_id = 0
OR
(devl_project_id <> 0 AND EXISTS
(SELECT 1 FROM cz_devl_projects
WHERE deleted_flag = '0'
AND devl_project_id = a.devl_project_id
AND orig_sys_ref = p_orig_sys_ref)))
AND ROWNUM < 2;
UPDATE CZ_IMP_DEVL_PROJECT SET
DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
SEEDED_FLAG=DECODE(SEEDED_FLAG,NULL,'0',SEEDED_FLAG),
DISPOSITION=l_disposition,
REC_STATUS=l_rec_status
WHERE ROWID = p_imp_devl_project.ROWID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2,
p_rp_folder_id IN NUMBER,
inCONFIG_ENGINE_TYPE IN VARCHAR2
) IS
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /* COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /* Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /* Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
KRS_DEVL_PROJECT(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,NOCHANGE,inXFR_GROUP,inCONFIG_ENGINE_TYPE);
XFR_DEVL_PROJECT(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,
nXfrUpdateCount,FAILED,nNoChangeCount,inXFR_GROUP, p_rp_folder_id);
/* Report Insert Errors */
IF(nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT:INSERTS ',11276,inRun_Id);
/* Report Update Errors */
IF(nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT:UPDATES ',11276,inRun_Id);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2,
p_rp_folder_id IN NUMBER
) IS
CURSOR c_xfr_devl_project IS
SELECT * FROM CZ_IMP_DEVL_PROJECT
WHERE Run_ID=inRUN_ID AND rec_status='PASS'
ORDER BY model_id, plan_level;
nInsertCount PLS_INTEGER:=0; -- Inserts --
nUpdateCount PLS_INTEGER:=0; -- Updates --
NOUPDATE_NAME NUMBER;
NOUPDATE_VERSION NUMBER;
NOUPDATE_INTL_TEXT_ID NUMBER;
NOUPDATE_CREATION_DATE NUMBER;
NOUPDATE_LAST_UPDATE_DATE NUMBER;
NOUPDATE_CREATED_BY NUMBER;
NOUPDATE_LAST_UPDATED_BY NUMBER;
NOUPDATE_DELETED_FLAG NUMBER;
NOUPDATE_ORIG_SYS_REF NUMBER;
NOUPDATE_DESC_TEXT NUMBER;
NOUPDATE_MODEL_TYPE NUMBER;
NOUPDATE_PRODUCT_KEY NUMBER;
NOUPDATE_ORGANIZATION_ID NUMBER;
NOUPDATE_INVENTORY_ITEM_ID NUMBER;
NOUPDATE_BOM_CPTN_RULE_ID NUMBER;
NOUPDATE_NONBOM_CPTN_RULE_ID NUMBER;
NOUPDATE_BOM_CPTN_TEXT_ID NUMBER;
NOUPDATE_NONBOM_CPTN_TEXT_ID NUMBER;
NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NAME',inXFR_GROUP);
NOUPDATE_VERSION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','VERSION',inXFR_GROUP);
NOUPDATE_INTL_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','INTL_TEXT_ID',inXFR_GROUP);
NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','CREATION_DATE',inXFR_GROUP);
NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','LAST_UPDATE_DATE',inXFR_GROUP);
NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','CREATED_BY',inXFR_GROUP);
NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','LAST_UPDATED_BY',inXFR_GROUP);
NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','DELETED_FLAG',inXFR_GROUP);
NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','ORIG_SYS_REF',inXFR_GROUP);
NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','DESC_TEXT',inXFR_GROUP);
NOUPDATE_MODEL_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','MODEL_TYPE',inXFR_GROUP);
NOUPDATE_INVENTORY_ITEM_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','INVENTORY_ITEM_ID',inXFR_GROUP);
NOUPDATE_ORGANIZATION_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','ORGANIZATION_ID',inXFR_GROUP);
NOUPDATE_PRODUCT_KEY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','PRODUCT_KEY',inXFR_GROUP);
NOUPDATE_BOM_CPTN_RULE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','BOM_CAPTION_RULE_ID',inXFR_GROUP);
NOUPDATE_NONBOM_CPTN_RULE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NONBOM_CAPTION_RULE_ID',inXFR_GROUP);
NOUPDATE_BOM_CPTN_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','BOM_CAPTION_TEXT_ID',inXFR_GROUP);
NOUPDATE_NONBOM_CPTN_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NONBOM_CAPTION_TEXT_ID',inXFR_GROUP);
UPDATE cz_imp_devl_project
SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
AND DISPOSITION=p_xfr_devl_project.disposition;
x_error:=CZ_UTILS.LOG_REPORT('Incorrect value of config_engine_type="'||p_xfr_devl_project.config_engine_type||'"',1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:INSERT',11276,inRun_Id);
UPDATE cz_imp_devl_project
SET REC_STATUS='OK'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID
AND RUN_ID=inRUN_ID
AND DISPOSITION='N';
UPDATE CZ_XFR_PROJECT_BILLS
SET LAST_IMPORT_RUN_ID=inRUN_ID,
LAST_IMPORT_DATE=SYSDATE
WHERE ORGANIZATION_ID=p_xfr_devl_project.ORGANIZATION_ID AND
TOP_ITEM_ID=p_xfr_devl_project.TOP_ITEM_ID AND
EXPLOSION_TYPE=p_xfr_devl_project.EXPLOSION_TYPE AND
MODEL_PS_NODE_ID = p_xfr_devl_project.devl_project_id
RETURNING copy_addl_child_models, source_server INTO copy_child_models, server_id;
INSERT INTO cz_devl_projects (devl_project_id, intl_text_id,
name, version, deleted_flag, orig_sys_ref, desc_text,
creation_date, last_update_date, created_by, last_updated_by,
persistent_project_id, model_type, organization_id, inventory_item_id, product_key,
bom_caption_rule_id, nonbom_caption_rule_id, config_engine_type, import_enabled)
VALUES
(p_xfr_devl_project.devl_project_id,
p_xfr_devl_project.intl_text_id,
p_xfr_devl_project.name,
p_xfr_devl_project.version,
p_xfr_devl_project.deleted_flag,
p_xfr_devl_project.orig_sys_ref,
p_xfr_devl_project.desc_text,
sysdate, sysdate, -UID, -UID, p_xfr_devl_project.persistent_project_id,
p_xfr_devl_project.model_type, p_xfr_devl_project.organization_id,
p_xfr_devl_project.inventory_item_id, p_xfr_devl_project.product_key,
p_xfr_devl_project.bom_caption_rule_id, p_xfr_devl_project.nonbom_caption_rule_id, p_xfr_devl_project.config_engine_type, '1');
nInsertCount:=nInsertCount+1;
UPDATE cz_imp_devl_project
SET REC_STATUS='OK'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
INSERT INTO CZ_RULE_FOLDERS
(RULE_FOLDER_ID,NAME,TREE_SEQ,DEVL_PROJECT_ID,CREATED_BY,LAST_UPDATED_BY,
CREATION_DATE,LAST_UPDATE_DATE,DELETED_FLAG)
SELECT CZ_RULE_FOLDERS_S.NEXTVAL,p_xfr_devl_project.name||' Rules',0,
p_xfr_devl_project.devl_project_id,UID,UID,sysdate,sysdate,'0'
FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM CZ_RULE_FOLDERS WHERE
DEVL_PROJECT_ID=p_xfr_devl_project.devl_project_id AND
PARENT_RULE_FOLDER_ID IS NULL AND NAME=p_xfr_devl_project.name||' Rules'
AND deleted_flag = '0');
INSERT INTO CZ_RP_ENTRIES
(OBJECT_TYPE,OBJECT_ID,ENCLOSING_FOLDER,NAME,DESCRIPTION,DELETED_FLAG,SEEDED_FLAG)
SELECT 'PRJ',p_xfr_devl_project.devl_project_id,p_rp_folder_id,
p_xfr_devl_project.name,p_xfr_devl_project.desc_text,'0',p_xfr_devl_project.seeded_flag
FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM CZ_RP_ENTRIES WHERE deleted_flag = '0' AND (
(OBJECT_TYPE='PRJ' AND OBJECT_ID=p_xfr_devl_project.devl_project_id) OR
(ENCLOSING_FOLDER=p_rp_folder_id AND NAME=p_xfr_devl_project.name)));
UPDATE CZ_XFR_PROJECT_BILLS SET
MODEL_PS_NODE_ID=p_xfr_devl_project.devl_project_id,
DESCRIPTION=p_xfr_devl_project.desc_text,
COMPONENT_ITEM_ID=p_xfr_devl_project.top_item_id,
LAST_IMPORT_RUN_ID=inRUN_ID,
LAST_IMPORT_DATE=SYSDATE
WHERE ORGANIZATION_ID=p_xfr_devl_project.ORGANIZATION_ID AND
TOP_ITEM_ID=p_xfr_devl_project.TOP_ITEM_ID AND
EXPLOSION_TYPE=p_xfr_devl_project.EXPLOSION_TYPE AND
MODEL_PS_NODE_ID = p_xfr_devl_project.model_id
RETURNING copy_addl_child_models,source_server INTO copy_child_models, server_id;
INSERT INTO cz_xfr_project_bills
(model_ps_node_id, description, component_item_id, last_import_run_id,
last_import_date, organization_id, top_item_id, explosion_type,
copy_addl_child_models, source_server, deleted_flag)
SELECT p_xfr_devl_project.devl_project_id, p_xfr_devl_project.desc_text,
p_xfr_devl_project.top_item_id, inRUN_ID, SYSDATE,
NVL(p_xfr_devl_project.ORGANIZATION_ID, 0), NVL(p_xfr_devl_project.TOP_ITEM_ID, 0),
NVL(p_xfr_devl_project.EXPLOSION_TYPE, 'GENERIC'), '0', NVL(server_id, 0), '0'
FROM DUAL WHERE NOT EXISTS
(SELECT NULL FROM cz_xfr_project_bills
WHERE model_ps_node_id = p_xfr_devl_project.devl_project_id
AND deleted_flag = '0');
UPDATE cz_imp_devl_project SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:INSERT',11276,inRun_Id);
UPDATE cz_devl_projects SET
intl_text_id=DECODE(NOUPDATE_INTL_TEXT_ID,0,p_xfr_devl_project.intl_text_id,intl_text_id),
name=DECODE(NOUPDATE_NAME,0,p_xfr_devl_project.name,name),
version=DECODE(NOUPDATE_VERSION,0,p_xfr_devl_project.version,version),
deleted_flag=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_devl_project.deleted_flag,deleted_flag),
orig_sys_ref=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_devl_project.orig_sys_ref,orig_sys_ref),
desc_text=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,desc_text),
LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,sysdate,LAST_UPDATE_DATE),
LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
MODEL_TYPE = DECODE(NOUPDATE_MODEL_TYPE,0,p_xfr_devl_project.model_type),
organization_id=DECODE(NOUPDATE_ORGANIZATION_ID,0,p_xfr_devl_project.organization_id,organization_id),
inventory_item_id=DECODE(NOUPDATE_INVENTORY_ITEM_ID,0,p_xfr_devl_project.inventory_item_id,inventory_item_id),
product_key= DECODE(NOUPDATE_PRODUCT_KEY,0,p_xfr_devl_project.product_key,product_key),
bom_caption_rule_id= DECODE(NOUPDATE_BOM_CPTN_RULE_ID,0,p_xfr_devl_project.bom_caption_rule_id,bom_caption_rule_id),
nonbom_caption_rule_id= DECODE(NOUPDATE_NONBOM_CPTN_RULE_ID,0,p_xfr_devl_project.nonbom_caption_rule_id,nonbom_caption_rule_id)
WHERE devl_project_id=p_xfr_devl_project.devl_project_id;
nUpdateCount:=nUpdateCount+1;
UPDATE cz_imp_devl_project
SET REC_STATUS='OK'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
UPDATE CZ_RP_ENTRIES SET
NAME = DECODE(NOUPDATE_NAME,0,p_xfr_devl_project.name,name),
DESCRIPTION = DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,description),
DELETED_FLAG = '0',
SEEDED_FLAG = p_xfr_devl_project.seeded_flag
WHERE OBJECT_TYPE='PRJ' AND OBJECT_ID=p_xfr_devl_project.devl_project_id
AND NOT EXISTS
(SELECT 1 FROM CZ_RP_ENTRIES
WHERE ENCLOSING_FOLDER=0
AND NAME=p_xfr_devl_project.name
AND deleted_flag = '0');
UPDATE CZ_XFR_PROJECT_BILLS SET
DESCRIPTION=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,description),
COMPONENT_ITEM_ID=p_xfr_devl_project.top_item_id,
LAST_IMPORT_RUN_ID=inRUN_ID,
LAST_IMPORT_DATE=SYSDATE,
deleted_flag = '0'
WHERE ORGANIZATION_ID=p_xfr_devl_project.ORGANIZATION_ID AND
TOP_ITEM_ID=p_xfr_devl_project.TOP_ITEM_ID AND
EXPLOSION_TYPE=p_xfr_devl_project.EXPLOSION_TYPE AND
MODEL_PS_NODE_ID = p_xfr_devl_project.devl_project_id
RETURNING copy_addl_child_models, source_server INTO copy_child_models, server_id;
UPDATE cz_imp_devl_project SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:UPDATE',11276,inRun_Id);
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM cz_imp_devl_project
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
TYPE tStringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
SELECT PLAN_LEVEL,ORIG_SYS_REF,USER_STR03,DEVL_PROJECT_ID,PS_NODE_TYPE,NAME,
FSK_INTLTEXT_1_1,FSK_INTLTEXT_1_EXT,
FSK_ITEMMASTER_2_1,FSK_ITEMMASTER_2_EXT,
FSK_PSNODE_3_1,FSK_PSNODE_3_EXT,
FSK_PSNODE_4_1,FSK_PSNODE_4_EXT,
FSK_DEVLPROJECT_5_1,FSK_DEVLPROJECT_5_EXT,
fsk_psnode_6_1, COMPONENT_SEQUENCE_PATH, ROWID, MINIMUM, MAXIMUM,
nvl(SRC_APPLICATION_ID, cnDefSrcAppId),
nvl(FSK_ITEMMASTER_2_2, cnDefSrcAppId),INTL_TEXT_ID,ITEM_ID,
INITIAL_VALUE,PARENT_ID,PRODUCT_FLAG,REFERENCE_ID,SYSTEM_NODE_FLAG,
TREE_SEQ,BOM_TREATMENT,COMPONENT_SEQUENCE_ID,BOM_REQUIRED,
SO_ITEM_TYPE_CODE,MINIMUM_SELECTED,MAXIMUM_SELECTED,EFFECTIVE_FROM,EFFECTIVE_UNTIL,
DECIMAL_QTY_FLAG,PRIMARY_UOM_CODE,BOM_SORT_ORDER,IB_TRACKABLE,
INITIAL_NUM_VALUE,SHIPPABLE_ITEM_FLAG,INVENTORY_TRANSACTABLE_FLAG,
SERIALIZABLE_ITEM_FLAG
FROM CZ_IMP_PS_NODES WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY PLAN_LEVEL,
DECODE(x_usesurr_psnode,0,ORIG_SYS_REF,1,USER_STR03),
DECODE(x_usesurr_intltext,0,FSK_INTLTEXT_1_1,1,FSK_INTLTEXT_1_EXT),
DECODE(x_usesurr_itemmaster,0,FSK_ITEMMASTER_2_1,1,FSK_ITEMMASTER_2_EXT),
DECODE(x_usesurr_psnode,0,FSK_PSNODE_3_1,1,FSK_PSNODE_3_EXT),
DECODE(x_usesurr_devlproject,0,FSK_DEVLPROJECT_5_1,1,FSK_DEVLPROJECT_5_EXT),
ROWID;
nOnlMinSel CZ_IMP_PS_NODES.MINIMUM_SELECTED%TYPE;
nImpMinSel CZ_IMP_PS_NODES.MINIMUM_SELECTED%TYPE;
nOnlMaxSel CZ_IMP_PS_NODES.MAXIMUM_SELECTED%TYPE;
nImpMaxSel CZ_IMP_PS_NODES.MAXIMUM_SELECTED%TYPE;
cDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
cDummyDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
SELECT VALUE INTO sPsNodeName FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
SELECT ADD_MONTHS(sysdate,300) INTO nEffUntilCap FROM DUAL;
SELECT ITEM_ID,REF_PART_NBR FROM CZ_ITEM_MASTERS
WHERE ORIG_SYS_REF=sFSKITEMMASTER21 AND deleted_flag = '0'
AND SRC_APPLICATION_ID=nFSKITEMMASTER22;
SELECT ITEM_ID,REF_PART_NBR FROM CZ_ITEM_MASTERS
WHERE ORIG_SYS_REF=sFSKITEMMASTER2EXT AND deleted_flag = '0'
AND SRC_APPLICATION_ID=nFSKITEMMASTER22;
SELECT PS_NODE_ID, PLAN_LEVEL FROM CZ_IMP_PS_NODES WHERE ORIG_SYS_REF=DECODE(x_usesurr_psnode, 0, sFSKPSNODE31, 1, sFSKPSNODE3EXT)
AND DEVL_PROJECT_ID=nDevlProjectId AND RUN_ID=inRUN_ID
AND ps_node_id IS NOT NULL
AND src_application_id = nImpSrcApplicationId
AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(SUBSTR(nImpTreeSeq, 1, INSTR(nImpTreeSeq, '-', -1, 1) - 1), -1);
SELECT PS_NODE_ID,DEVL_PROJECT_ID,INTL_TEXT_ID,ITEM_ID,NAME,
ORIG_SYS_REF,INITIAL_VALUE,PARENT_ID,MINIMUM,MAXIMUM,PS_NODE_TYPE,PRODUCT_FLAG,REFERENCE_ID,
SYSTEM_NODE_FLAG,TREE_SEQ,BOM_TREATMENT,COMPONENT_SEQUENCE_ID,BOM_REQUIRED_FLAG,SO_ITEM_TYPE_CODE,MINIMUM_SELECTED,
MAXIMUM_SELECTED,EFFECTIVE_FROM,EFFECTIVE_UNTIL,DECIMAL_QTY_FLAG,COMPONENT_SEQUENCE_PATH,PRIMARY_UOM_CODE,BOM_SORT_ORDER,
IB_TRACKABLE,INITIAL_NUM_VALUE,SRC_APPLICATION_ID,SHIPPABLE_ITEM_FLAG,INVENTORY_TRANSACTABLE_FLAG,SERIALIZABLE_ITEM_FLAG
FROM CZ_PS_NODES
WHERE ORIG_SYS_REF=DECODE(x_usesurr_psnode,0, sImpOrigsysref, 1, sImpUserstr03)
AND DEVL_PROJECT_ID = nDevlProjectId
AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(nImpTreeSeq, -1)
AND deleted_flag = '0'
AND src_application_id = nImpSrcApplicationId;
SELECT PS_NODE_ID, devl_project_id, item_id, ib_trackable FROM CZ_IMP_PS_NODES
WHERE ORIG_SYS_REF = sFSKREFERENCE
AND RUN_ID=inRun_ID
AND ps_node_id IS NOT NULL;
SELECT model_type FROM cz_imp_devl_project
WHERE devl_project_id = nDevlProjectId
AND RUN_ID=inRun_ID;
SELECT name FROM cz_imp_devl_project
WHERE devl_project_id = nOnlDevlProjectId
AND RUN_ID=inRun_ID;
SELECT ref_part_nbr FROM cz_item_masters
WHERE item_id = nReferredItemId;
SELECT p.PS_NODE_ID, p.devl_project_id, p.item_id FROM CZ_PS_NODES p, CZ_DEVL_PROJECTS d
WHERE p.ORIG_SYS_REF = sFSKREFERENCE
AND p.ps_node_id = p.persistent_node_id
AND p.deleted_flag = '0'
AND p.ps_node_id = d.devl_project_id
AND d.deleted_flag = '0';
/* Insert or update */
BEGIN
IF((sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) AND
((nLastTreeSeq IS NULL AND nPsNodeType = bomModel) OR
(nLastTreeSeq IS NOT NULL AND nLastTreeSeq = nThisTreeSeq))) THEN
/* This is a duplicate record */
sRecStatus:='DUPL';
SELECT
DECODE(x_usesurr_psnode,1,DECODE(sDISPOSITION,NULL,sImpUserStr03,sImpOrigsysref), sImpOrigsysref),
DECODE(inXFR_GROUP,'GENERIC',nImpName,
NamePrefix || DECODE(nPsNodeType,cnReference,DECODE(localName,NULL,nImpName,localName),
DECODE(sOnlItemRefPartNbr,NULL,nImpName,
DECODE(sPsNodeName,'DESCRIPTION',nImpName,sOnlItemRefPartNbr)))),
DECODE(nPsNodeType,cnReference,nOnlReference,nImpReferenceId)
INTO tmpOrigSysRef, tmpName, tmpReferenceId
FROM DUAL;
nUpdateCount:=nUpdateCount+1;
to update model_ref_expl_id of rules' participants
IF(nPsNodeType = cnReference)THEN
UPDATE cz_ps_nodes SET deleted_flag='1' WHERE ps_node_id = nOnlPsnodeId;
cz_refs.delete_Node(nOnlPsnodeId, cnReference, p_out_err, '1');
nInsertCount:=nInsertCount+1;
SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
/*Insert */
nDebug := 1022;
nInsertCount:=nInsertCount+1;
SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
SELECT nvl(MINIMUM,0), nvl(MAXIMUM,-1) INTO sMinimum, sMaximum
FROM CZ_PS_NODES
WHERE PS_NODE_ID = nOnlPsnodeId
AND DELETED_FLAG = '0';
-- Don't update min/max for references in a model (PTO)
-- For an existing reference, get min/max values from cz_ps_nodes
DECLARE
CURSOR c_node IS
SELECT model_type
FROM CZ_DEVL_PROJECTS
WHERE DEVL_PROJECT_ID IN (SELECT PARENT_ID FROM CZ_PS_NODES
WHERE PS_NODE_ID = nOnlPsnodeId
AND DELETED_FLAG = '0')
AND DELETED_FLAG = '0';
SELECT nvl(MINIMUM,1), nvl(MAXIMUM,1) INTO sMinimum, sMaximum
FROM CZ_PS_NODES
WHERE PS_NODE_ID = nOnlPsnodeId
AND DELETED_FLAG = '0';
select max(tree_seq) into nOnlMaxtreeSeq_forParent
from cz_ps_nodes
where parent_id = nImpparentid
and deleted_flag = '0';
UPDATE CZ_IMP_PS_NODES SET
ORIG_SYS_REF=DECODE(x_usesurr_psnode,1,DECODE(sDISPOSITION,NULL,sImpUserStr03,ORIG_SYS_REF), ORIG_SYS_REF),
PS_NODE_ID=DECODE(sDISPOSITION,'R',PS_NODE_ID,'I',
DECODE(PS_NODE_TYPE,bomModel,DEVL_PROJECT_ID,cnModel,DEVL_PROJECT_ID,nNextId),nOnlPsnodeId),
ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,nonlitemid),
NAME=DECODE(inXFR_GROUP,'GENERIC',NAME,
NamePrefix || DECODE(sDISPOSITION,'R',NAME,
DECODE(nPsNodeType,cnReference,DECODE(localName,NULL,NAME,localName),
DECODE(sOnlItemRefPartNbr,NULL,NAME,
DECODE(sPsNodeName,'DESCRIPTION',NAME,sOnlItemRefPartNbr))))),
PARENT_ID=DECODE(sDISPOSITION,'R',PARENT_ID,nImpparentid),
REFERENCE_ID=DECODE(sDISPOSITION,'R',REFERENCE_ID,DECODE(PS_NODE_TYPE,cnReference,nOnlReference,REFERENCE_ID)),
MINIMUM = DECODE(nPsNodeType,cnReference,sMinimum, MINIMUM),
MAXIMUM = DECODE(nPsNodeType,cnReference,sMaximum, MAXIMUM),
--------------------bug3495030
TREE_SEQ = DECODE(sDISPOSITION,'I',nvl(nNextTreeSeq,TREE_SEQ),TREE_SEQ),
DISPOSITION=sDisposition,
REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
SELECT DELETED_FLAG, SYSTEM_NODE_FLAG, SRC_APPLICATION_ID, ORIG_SYS_REF,
PS_NODE_TYPE, MINIMUM, MAXIMUM, INSTANTIABLE_FLAG, NAME, FSK_DEVLPROJECT_5_1,
FSK_PSNODE_3_1, FSK_PSNODE_3_EXT, REFERENCE_ID, INITIAL_NUM_VALUE, DECIMAL_QTY_FLAG,
MINIMUM_SELECTED, MAXIMUM_SELECTED, UI_OMIT, DISPLAY_IN_SUMMARY_FLAG, ROWID
FROM CZ_IMP_PS_NODES
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
l_minimum_selected cz_imp_ps_nodes.minimum_selected%TYPE;
l_maximum_selected cz_imp_ps_nodes.maximum_selected%TYPE;
UPDATE cz_imp_ps_nodes a
SET disposition = 'R', rec_status = 'DUP'
WHERE run_id = inRun_ID
AND rec_status IS NULL
AND EXISTS (SELECT count(*), orig_sys_ref,fsk_devlproject_5_1,src_application_id
FROM cz_imp_ps_nodes
WHERE run_id = a.run_id
AND rec_status IS NULL
AND orig_sys_ref = a.orig_sys_ref
AND fsk_devlproject_5_1 = a.fsk_devlproject_5_1
AND src_application_id = a.src_application_id
AND src_application_id <> 702
GROUP BY orig_sys_ref, fsk_devlproject_5_1, src_application_id
HAVING count(*) > 1);
l_minimum_selected := p_imp_psnode.minimum_selected;
l_maximum_selected := p_imp_psnode.maximum_selected;
l_minimum_selected := CEIL(p_imp_psnode.minimum_selected);
IF (p_imp_psnode.maximum_selected <> -1) THEN
l_maximum_selected := FLOOR(p_imp_psnode.maximum_selected);
l_maximum_selected := p_imp_psnode.maximum_selected;
IF (l_minimum_selected > l_maximum_selected AND l_maximum_selected <> -1) THEN
l_disposition := 'R';
UPDATE cz_imp_ps_nodes SET
deleted_flag=DECODE(deleted_flag,NULL,'0',deleted_flag),
system_node_flag=DECODE(system_node_flag,NULL,'0',SYSTEM_NODE_FLAG),
instantiable_flag=DECODE(instantiable_flag,NULL,sInstantiableFlag,instantiable_flag),
src_application_id=DECODE(src_application_id,NULL,cnDefSrcAppId,src_application_id),
minimum=l_minimum,
maximum=l_maximum,
minimum_selected=l_minimum_selected,
maximum_selected=l_maximum_selected,
disposition=l_disposition,
rec_status=l_rec_status
WHERE ROWID = p_imp_psnode.ROWID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
KRS_PS_NODE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,NOCHANGE,inXFR_GROUP);
SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
INTO genStatisticsCz FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
XFR_PS_NODE (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,nNoChangeCount,inXFR_GROUP);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'IMP_IM_PS_NODE.MAIN_PS_NODE',11276,inRun_Id);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'IMP_IM_PS_NODE.MAIN_PS_NODE',11276,inRun_Id);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,--VR
inXFR_GROUP IN VARCHAR2
) IS
TYPE tPsNodeId IS TABLE OF cz_imp_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
TYPE tDeletedFlag IS TABLE OF cz_imp_ps_nodes.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE tLastUpdateDate IS TABLE OF cz_imp_ps_nodes.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
TYPE tLastUpdatedBy IS TABLE OF cz_imp_ps_nodes.LAST_UPDATED_BY%TYPE INDEX BY BINARY_INTEGER;
TYPE tMinimumSelected IS TABLE OF cz_imp_ps_nodes.MINIMUM_SELECTED%TYPE INDEX BY BINARY_INTEGER;
TYPE tMaximumSelected IS TABLE OF cz_imp_ps_nodes.MAXIMUM_SELECTED%TYPE INDEX BY BINARY_INTEGER;
iDeletedFlag tDeletedFlag;
iLastUpdateDate tLastUpdateDate;
iLastUpdatedBy tLastUpdatedBy;
iMinimumSelected tMinimumSelected;
iMaximumSelected tMaximumSelected;
SELECT plan_level, ps_node_id, devl_project_id, reference_id,
minimum, maximum, ps_node_type, parent_id, disposition
FROM cz_imp_ps_nodes
WHERE ps_node_type IN (cnReference,cnConnector,cnComponent)
AND deleted_flag='0' AND rec_status='OK' AND run_id=inRun_ID
ORDER BY plan_level, devl_project_id, reference_id;
SELECT REFS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition, REFS.devl_project_id AS REFERRING_MODEL_ID,
ROOTS.ps_node_type, ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, ROOTS.name, d.model_type
FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS,cz_imp_devl_project d
WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
AND ROOTS.devl_project_id=REFS.reference_id
AND NOT EXISTS (SELECT 1 FROM cz_imp_ps_nodes
WHERE run_id=inRUN_ID
AND rec_status='PASS'
AND devl_project_id=ROOTS.devl_project_id
AND ps_node_type IN (cnReference,cnConnector))
AND ROOTS.run_id = inRUN_ID
AND ROOTS.rec_status='PASS'
AND ROOTS.devl_project_id = d.devl_project_id
AND d.rec_status = 'OK'
AND d.run_id = inRun_ID
AND REFS.run_id = inRUN_ID
AND REFS.rec_status = 'PASS'
ORDER BY ROOTS.devl_project_id;
SELECT REFS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition,
REFS.devl_project_id AS REFERRING_MODEL_ID, ROOTS.ps_node_type,
ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, ROOTS.name, d.model_type
FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS, cz_imp_devl_project d
WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
AND ROOTS.ps_node_id = REFS.reference_id
AND REFS.ps_node_type IN (cnReference,cnConnector)
AND EXISTS (SELECT 1 FROM cz_imp_ps_nodes
WHERE run_id=inRUN_ID
AND rec_status='PASS'
AND devl_project_id=ROOTS.devl_project_id
AND ps_node_type IN (cnReference,cnConnector))
AND ROOTS.run_id=inRUN_ID
AND ROOTS.rec_status='PASS'
AND REFS.rec_status='PASS'
AND REFS.run_id=inRUN_ID
AND ROOTS.devl_project_id = d.devl_project_id
AND d.run_id=inRUN_ID
AND d.rec_status='OK'
ORDER BY ROOTS.devl_project_id;
SELECT ROOTS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition,
REFS.reference_id, ROOTS.ps_node_type, ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, d.model_type
FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS, cz_imp_devl_project d
WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
AND ROOTS.ps_node_id = REFS.devl_project_id
AND REFS.ps_node_type IN (cnReference,cnConnector)
AND ROOTS.run_id=inRUN_ID
AND ROOTS.rec_status='PASS'
AND ROOTS.devl_project_id = d.devl_project_id
AND d.run_id=inRUN_ID
AND d.rec_status='OK'
AND REFS.rec_status='PASS'
AND REFS.run_id=inRUN_ID
AND NOT EXISTS (SELECT 1 FROM cz_imp_ps_nodes
WHERE run_id=inRUN_ID
AND rec_status='PASS'
AND reference_id=ROOTS.devl_project_id
AND ps_node_type IN (cnReference,cnConnector))
ORDER BY ROOTS.devl_project_id;
SELECT p.plan_level, p.devl_project_id, p.ps_node_id, p.disposition,
p.ps_node_type, p.minimum, p.maximum, p.rec_status, d.model_type
FROM cz_imp_ps_nodes p, cz_imp_devl_project d
WHERE p.run_id=inRUN_ID
AND p.rec_status='PASS'
AND (p.ps_node_id = p.devl_project_id OR (p.parent_id IS NULL AND p.plan_level=0))
AND p.devl_project_id=d.devl_project_id
AND d.run_id=inRUN_ID
AND d.rec_status='OK';
SELECT PS_NODE_ID,DEVL_PROJECT_ID,FROM_POPULATOR_ID,PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,INTL_TEXT_ID,SUB_CONS_ID,ITEM_ID,NAME,RESOURCE_FLAG,
INITIAL_VALUE,initial_num_value, PARENT_ID,MINIMUM,MAXIMUM,PS_NODE_TYPE,FEATURE_TYPE,
PRODUCT_FLAG,REFERENCE_ID,MULTI_CONFIG_FLAG,ORDER_SEQ_FLAG,SYSTEM_NODE_FLAG,TREE_SEQ,
COUNTED_OPTIONS_FLAG,UI_OMIT,UI_SECTION,BOM_TREATMENT,ORIG_SYS_REF,CHECKOUT_USER,
DISPOSITION,DELETED_FLAG,EFFECTIVE_FROM,EFFECTIVE_UNTIL,EFFECTIVE_USAGE_MASK,USER_STR01,USER_STR02,USER_STR03,
USER_STR04,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,CREATION_DATE,LAST_UPDATE_DATE,
CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK, PLAN_LEVEL, SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,MAXIMUM_SELECTED,BOM_REQUIRED,COMPONENT_SEQUENCE_ID,
ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DECIMAL_QTY_FLAG,INSTANTIABLE_FLAG,
QUOTEABLE_FLAG,PRIMARY_UOM_CODE,BOM_SORT_ORDER,COMPONENT_SEQUENCE_PATH,IB_TRACKABLE, SRC_APPLICATION_ID,DISPLAY_IN_SUMMARY_FLAG,
IB_LINK_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
INVENTORY_TRANSACTABLE_FLAG,
ASSEMBLE_TO_ORDER_FLAG,
SERIALIZABLE_ITEM_FLAG
FROM CZ_IMP_PS_NODES
WHERE CZ_IMP_PS_NODES.RUN_ID = inRUN_ID AND REC_STATUS='PASS'
AND devl_project_id=inModelId AND disposition=inDisposition
ORDER BY PLAN_LEVEL,USER_NUM04 DESC;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
NOUPDATE_PS_NODE_ID NUMBER;
NOUPDATE_DEVL_PROJECT_ID NUMBER;
NOUPDATE_FROM_POPULATOR_ID NUMBER;
NOUPDATE_PROPERTY_BACKPTR NUMBER;
NOUPDATE_ITEM_TYPE_BACKPTR NUMBER;
NOUPDATE_INTL_TEXT_ID NUMBER;
NOUPDATE_SUB_CONS_ID NUMBER;
NOUPDATE_ITEM_ID NUMBER;
NOUPDATE_NAME NUMBER;
NOUPDATE_RESOURCE_FLAG NUMBER;
NOUPDATE_INITIAL_VALUE NUMBER;
NOUPDATE_INITIAL_NUM_VALUE NUMBER;
NOUPDATE_PARENT_ID NUMBER;
NOUPDATE_MINIMUM NUMBER;
NOUPDATE_MAXIMUM NUMBER;
NOUPDATE_PS_NODE_TYPE NUMBER;
NOUPDATE_FEATURE_TYPE NUMBER;
NOUPDATE_PRODUCT_FLAG NUMBER;
NOUPDATE_REFERENCE_ID NUMBER;
NOUPDATE_MULTI_CONFIG_FLAG NUMBER;
NOUPDATE_ORDER_SEQ_FLAG NUMBER;
NOUPDATE_SYSTEM_NODE_FLAG NUMBER;
NOUPDATE_TREE_SEQ NUMBER;
NOUPDATE_COUNTED_OPTIONS_FLAG NUMBER;
NOUPDATE_UI_OMIT NUMBER;
NOUPDATE_UI_SECTION NUMBER;
NOUPDATE_BOM_TREATMENT NUMBER;
NOUPDATE_ORIG_SYS_REF NUMBER;
NOUPDATE_CHECKOUT_USER NUMBER;
NOUPDATE_DELETED_FLAG NUMBER;
NOUPDATE_EFF_FROM NUMBER;
NOUPDATE_EFF_TO NUMBER;
NOUPDATE_EFF_MASK NUMBER;
NOUPDATE_USER_STR01 NUMBER;
NOUPDATE_USER_STR02 NUMBER;
NOUPDATE_USER_STR03 NUMBER;
NOUPDATE_USER_STR04 NUMBER;
NOUPDATE_USER_NUM01 NUMBER;
NOUPDATE_USER_NUM02 NUMBER;
NOUPDATE_USER_NUM03 NUMBER;
NOUPDATE_USER_NUM04 NUMBER;
NOUPDATE_CREATION_DATE NUMBER;
NOUPDATE_LAST_UPDATE_DATE NUMBER;
NOUPDATE_CREATED_BY NUMBER;
NOUPDATE_LAST_UPDATED_BY NUMBER;
NOUPDATE_SECURITY_MASK NUMBER;
NOUPDATE_SO_ITEM_TYPE_CODE NUMBER;
NOUPDATE_MINIMUM_SELECTED NUMBER;
NOUPDATE_MAXIMUM_SELECTED NUMBER;
NOUPDATE_BOM_REQUIRED NUMBER;
NOUPDATE_COMPONENT_SEQUENCE_ID NUMBER;
NOUPDATE_DECIMAL_QTY_FLAG NUMBER;
NOUPDATE_QUOTEABLE_FLAG NUMBER;
NOUPDATE_PRIMARY_UOM_CODE NUMBER;
NOUPDATE_BOM_SORT_ORDER NUMBER;
NOUPDATE_SEQUENCE_PATH NUMBER;
NOUPDATE_IB_TRACKABLE NUMBER;
NOUPDATE_DSPLY_SMRY_FLG NUMBER;
NOUPDATE_IBLINKITEM_FLG NUMBER;
NOUPDATE_INSTANTIABLE_FLAG NUMBER;
NOUPDATE_SHIPPABLE_ITEM_FLAG NUMBER;
NOUPDATE_INV_TXN_FLAG NUMBER;
NOUPDATE_ASM_TO_ORDER_FLAG NUMBER;
NOUPDATE_SERIAL_ITEM_FLAG NUMBER;
PROCEDURE insert_ps_nodes(p_model_id IN NUMBER, x_retcode OUT NOCOPY NUMBER)
IS
BEGIN
nCommitCount:=0;
LOOP -- bulk fetch for insert
iPSNODEID.DELETE; iDEVLPROJECTID.DELETE; iFROMPOPULATORID.DELETE; iPROPERTYBACKPTR.DELETE;
iITEMTYPEBACKPTR.DELETE; iINTLTEXTID.DELETE; iSUBCONSID.DELETE; iITEMID.DELETE; iNAME.DELETE; iRESOURCEFLAG.DELETE;
iINITIALVALUE.DELETE; iInitialnumvalue.DELETE; iPARENTID.DELETE; iMINIMUM.DELETE; iMAXIMUM.DELETE; iPSNODETYPE.DELETE;
iFEATURETYPE.DELETE; iPRODUCTFLAG.DELETE; iREFERENCEID.DELETE; iMULTICONFIGFLAG.DELETE; iORDERSEQFLAG.DELETE;
iSYSTEMNODEFLAG.DELETE; iTREESEQ.DELETE; iCOUNTEDOPTIONSFLAG.DELETE; iUIOMIT.DELETE; iUISECTION.DELETE; iBOMTREATMENT.DELETE;
iORIGSYSREF.DELETE; iCHECKOUTUSER.DELETE; iDISPOSITION.DELETE; iDELETEDFLAG.DELETE; iEFFECTIVEFROM.DELETE;
iEFFECTIVEUNTIL.DELETE; iEFFECTIVEUSAGEMASK.DELETE; iUSERSTR01.DELETE; iUSERSTR02.DELETE;
iUSERSTR03.DELETE; iUSERSTR04.DELETE; iUSERNUM01.DELETE; iUSERNUM02.DELETE; iUSERNUM03.DELETE; iUSERNUM04.DELETE;
iCREATIONDATE.DELETE; iLASTUPDATEDATE.DELETE; iCREATEDBY.DELETE; iLASTUPDATEDBY.DELETE; iSECURITYMASK.DELETE;
iPLANLEVEL.DELETE; iSOITEMTYPECODE.DELETE; iMINIMUMSELECTED.DELETE; iMAXIMUMSELECTED.DELETE; iBOMREQUIRED.DELETE;
iCOMPONENTSEQUENCEID.DELETE; iORGANIZATIONID.DELETE; iTOPITEMID.DELETE; iEXPLOSIONTYPE.DELETE; iDECIMALQTYFLAG.DELETE;
iINSTANTIABLEFLAG.DELETE; iQUOTEABLEFLAG.DELETE; iPRIMARYUOMCODE.DELETE; iBOMSORTORDER.DELETE;
iCOMPONENTSEQUENCEPATH.DELETE;iIBTRACKABLE.DELETE;iSRCAPPLICATIONID.DELETE;iDisplayInSummaryFlag.DELETE;iIBLinkItemFlag.DELETE;
iShippableItemFlag.DELETE;
iInventoryTransactableFlag.DELETE;
iAssembleToOrder.DELETE;
iSerializableItemFlag.DELETE;
iDISPOSITION,iDELETEDFLAG,iEFFECTIVEFROM,iEFFECTIVEUNTIL,iEFFECTIVEUSAGEMASK,iUSERSTR01,iUSERSTR02,iUSERSTR03,
iUSERSTR04,iUSERNUM01,iUSERNUM02,iUSERNUM03,iUSERNUM04,iCREATIONDATE,iLASTUPDATEDATE,
iCREATEDBY,iLASTUPDATEDBY,iSECURITYMASK, iPLANLEVEL, iSOITEMTYPECODE,
iMINIMUMSELECTED,iMAXIMUMSELECTED,iBOMREQUIRED,iCOMPONENTSEQUENCEID,
iORGANIZATIONID,iTOPITEMID,iEXPLOSIONTYPE,iDECIMALQTYFLAG,iINSTANTIABLEFLAG,
iQUOTEABLEFLAG,iPRIMARYUOMCODE,iBOMSORTORDER,iCOMPONENTSEQUENCEPATH,iIBTRACKABLE,iSRCAPPLICATIONID,iDisplayInSummaryFlag,
iIBLinkItemFlag,
iShippableItemFlag,
iInventoryTransactableFlag,
iAssembleToOrder,
iSerializableItemFlag
LIMIT COMMIT_SIZE;
IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
(iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
ROLLBACK;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
BEGIN -- bulk insert
FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
INSERT INTO CZ_PS_NODES (PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ITEM_ID,
NAME,
RESOURCE_FLAG,
INITIAL_VALUE,
initial_num_value,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,UI_SECTION,
BOM_TREATMENT,
ORIG_SYS_REF,
CHECKOUT_USER,
USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
--EFFECTIVE_USAGE_MASK,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED_FLAG,
COMPONENT_SEQUENCE_ID,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
BOM_SORT_ORDER,
COMPONENT_SEQUENCE_PATH,
IB_TRACKABLE,
SRC_APPLICATION_ID,
VIRTUAL_FLAG,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG,
IB_LINK_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
INVENTORY_TRANSACTABLE_FLAG,
ASSEMBLE_TO_ORDER_FLAG,
SERIALIZABLE_ITEM_FLAG)
VALUES
( iPSNODEID(j),
iDEVLPROJECTID(j),
iFROMPOPULATORID(j), iPROPERTYBACKPTR(j),
iITEMTYPEBACKPTR(j), iINTLTEXTID(j),
iSUBCONSID(j), iITEMID(j),
iNAME(j),iRESOURCEFLAG(j),
iINITIALVALUE(j), iINITIALNUMVALUE(j),
iPARENTID(j),
iMINIMUM(j), iMAXIMUM(j),
iPSNODETYPE(j), iFEATURETYPE(j),
iPRODUCTFLAG(j),iREFERENCEID(j),iMULTICONFIGFLAG(j),iORDERSEQFLAG(j),
iSYSTEMNODEFLAG(j),iTREESEQ(j), iCOUNTEDOPTIONSFLAG(j), iUIOMIT(j),
iUISECTION(j), iBOMTREATMENT(j),
iORIGSYSREF(j), iCHECKOUTUSER (j),iUSERNUM01(j),iUSERNUM02(j),
iUSERNUM03(j), iUSERNUM04(j),
iUSERSTR01(j), iUSERSTR02(j), iUSERSTR03(j), iUSERSTR04(j),
SYSDATE, SYSDATE,
iDELETEDFLAG(j),
iEFFECTIVEFROM (j), iEFFECTIVEUNTIL (j),
-UID, -UID, NULL,
-- iEFFECTIVEUSAGEMASK(j),
iSOITEMTYPECODE(j),
iMINIMUMSELECTED(j),
iMAXIMUMSELECTED(j),
iBOMREQUIRED(j),
iCOMPONENTSEQUENCEID(j),
iDECIMALQTYFLAG(j),
iQUOTEABLEFLAG(j),
iPRIMARYUOMCODE(j),
iBOMSORTORDER(j),
iCOMPONENTSEQUENCEPATH(j),
iIBTRACKABLE(j),
iSRCAPPLICATIONID(j),
sVirtualFlag,
iINSTANTIABLEFLAG(j),
iDisplayInSummaryFlag(j),
iIBLinkItemFlag(j),
iShippableItemFlag(j),
iInventoryTransactableFlag(j),
iAssembleToOrder(j),
iSerializableItemFlag(j));
nInsertCount:= nInsertCount + SQL%ROWCOUNT;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='OK'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
ROLLBACK; -- need to insert row by row to log errors
EXCEPTION -- bulk insert
WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
RAISE;
IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
(iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
ROLLBACK;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
BEGIN -- single row insert
INSERT INTO CZ_PS_NODES (PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ITEM_ID,
NAME,
RESOURCE_FLAG,
INITIAL_VALUE,
initial_num_value,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,UI_SECTION,
BOM_TREATMENT,
ORIG_SYS_REF,
CHECKOUT_USER,
USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
--EFFECTIVE_USAGE_MASK,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED_FLAG,
COMPONENT_SEQUENCE_ID,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
BOM_SORT_ORDER,
COMPONENT_SEQUENCE_PATH,
IB_TRACKABLE,
SRC_APPLICATION_ID,
VIRTUAL_FLAG,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG,
IB_LINK_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
INVENTORY_TRANSACTABLE_FLAG,
ASSEMBLE_TO_ORDER_FLAG,
SERIALIZABLE_ITEM_FLAG)
VALUES
( iPSNODEID(j),
iDEVLPROJECTID(j),
iFROMPOPULATORID(j), iPROPERTYBACKPTR(j),
iITEMTYPEBACKPTR(j), iINTLTEXTID(j),
iSUBCONSID(j), iITEMID(j),
iNAME(j),iRESOURCEFLAG(j),
iINITIALVALUE(j), iINITIALNUMVALUE(j),
iPARENTID(j),
iMINIMUM(j), iMAXIMUM(j),
iPSNODETYPE(j), iFEATURETYPE(j),
iPRODUCTFLAG(j),iREFERENCEID(j),iMULTICONFIGFLAG(j),iORDERSEQFLAG(j),
iSYSTEMNODEFLAG(j),iTREESEQ(j), iCOUNTEDOPTIONSFLAG(j), iUIOMIT(j),
iUISECTION(j), iBOMTREATMENT(j),
iORIGSYSREF(j), iCHECKOUTUSER (j),iUSERNUM01(j),iUSERNUM02(j),
iUSERNUM03(j), iUSERNUM04(j),
iUSERSTR01(j), iUSERSTR02(j), iUSERSTR03(j), iUSERSTR04(j),
SYSDATE, SYSDATE,
iDELETEDFLAG(j),
iEFFECTIVEFROM (j), iEFFECTIVEUNTIL (j),
-UID, -UID, NULL,
-- iEFFECTIVEUSAGEMASK(j),
iSOITEMTYPECODE(j),
iMINIMUMSELECTED(j),
iMAXIMUMSELECTED(j),
iBOMREQUIRED(j),
iCOMPONENTSEQUENCEID(j),
iDECIMALQTYFLAG(j),
iQUOTEABLEFLAG(j),
iPRIMARYUOMCODE(j),
iBOMSORTORDER(j),
iCOMPONENTSEQUENCEPATH(j),
iIBTRACKABLE(j),
iSRCAPPLICATIONID(j),
sVirtualFlag,
iINSTANTIABLEFLAG(j),
iDisplayInSummaryFlag(j),
iIBLinkItemFlag(j),
iShippableItemFlag(j),
iInventoryTransactableFlag(j),
iAssembleToOrder(j),
iSerializableItemFlag(j));
nInsertCount:=nInsertCount+1;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='OK'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
EXCEPTION -- single row insert
WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
RAISE;
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=p_model_id
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='I';
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=p_model_id
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
END; --single row insert
END; -- bulk insert
END LOOP; -- bulk fetch for insert
END insert_ps_nodes;
PROCEDURE update_ps_nodes(p_model_id IN NUMBER)
IS
BEGIN
IF c_xfr_psnode%ISOPEN THEN
CLOSE c_xfr_psnode;
LOOP -- bulk fetch for update
iPSNODEID.DELETE; iDEVLPROJECTID.DELETE; iFROMPOPULATORID.DELETE; iPROPERTYBACKPTR.DELETE;
iITEMTYPEBACKPTR.DELETE; iINTLTEXTID.DELETE; iSUBCONSID.DELETE; iITEMID.DELETE; iNAME.DELETE; iRESOURCEFLAG.DELETE;
iINITIALVALUE.DELETE; iInitialnumvalue.DELETE; iPARENTID.DELETE; iMINIMUM.DELETE; iMAXIMUM.DELETE; iPSNODETYPE.DELETE;
iFEATURETYPE.DELETE; iPRODUCTFLAG.DELETE; iREFERENCEID.DELETE; iMULTICONFIGFLAG.DELETE; iORDERSEQFLAG.DELETE;
iSYSTEMNODEFLAG.DELETE; iTREESEQ.DELETE; iCOUNTEDOPTIONSFLAG.DELETE; iUIOMIT.DELETE; iUISECTION.DELETE; iBOMTREATMENT.DELETE;
iORIGSYSREF.DELETE; iCHECKOUTUSER.DELETE; iDISPOSITION.DELETE; iDELETEDFLAG.DELETE; iEFFECTIVEFROM.DELETE;
iEFFECTIVEUNTIL.DELETE; iEFFECTIVEUSAGEMASK.DELETE; iUSERSTR01.DELETE; iUSERSTR02.DELETE;
iUSERSTR03.DELETE; iUSERSTR04.DELETE; iUSERNUM01.DELETE; iUSERNUM02.DELETE; iUSERNUM03.DELETE; iUSERNUM04.DELETE;
iCREATIONDATE.DELETE; iLASTUPDATEDATE.DELETE; iCREATEDBY.DELETE; iLASTUPDATEDBY.DELETE; iSECURITYMASK.DELETE;
iPLANLEVEL.DELETE; iSOITEMTYPECODE.DELETE; iMINIMUMSELECTED.DELETE; iMAXIMUMSELECTED.DELETE; iBOMREQUIRED.DELETE;
iCOMPONENTSEQUENCEID.DELETE; iORGANIZATIONID.DELETE; iTOPITEMID.DELETE; iEXPLOSIONTYPE.DELETE; iDECIMALQTYFLAG.DELETE;
iINSTANTIABLEFLAG.DELETE; iQUOTEABLEFLAG.DELETE; iPRIMARYUOMCODE.DELETE; iBOMSORTORDER.DELETE;
iCOMPONENTSEQUENCEPATH.DELETE;iIBTRACKABLE.DELETE;iSRCAPPLICATIONID.DELETE;iDisplayInSummaryFlag.DELETE;iIBLinkItemFlag.DELETE;
iShippableItemFlag.DELETE;
iInventoryTransactableFlag.DELETE;
iAssembleToOrder.DELETE;
iSerializableItemFlag.DELETE;
iDISPOSITION,iDELETEDFLAG,iEFFECTIVEFROM,iEFFECTIVEUNTIL,iEFFECTIVEUSAGEMASK,iUSERSTR01,iUSERSTR02,iUSERSTR03,
iUSERSTR04,iUSERNUM01,iUSERNUM02,iUSERNUM03,iUSERNUM04,iCREATIONDATE,iLASTUPDATEDATE,
iCREATEDBY,iLASTUPDATEDBY,iSECURITYMASK, iPLANLEVEL, iSOITEMTYPECODE,
iMINIMUMSELECTED,iMAXIMUMSELECTED,iBOMREQUIRED,iCOMPONENTSEQUENCEID,
iORGANIZATIONID,iTOPITEMID,iEXPLOSIONTYPE,iDECIMALQTYFLAG,iINSTANTIABLEFLAG,
iQUOTEABLEFLAG,iPRIMARYUOMCODE,iBOMSORTORDER,iCOMPONENTSEQUENCEPATH,iIBTRACKABLE,iSRCAPPLICATIONID,iDisplayInSummaryFlag,
iIBLinkItemFlag,
iShippableItemFlag,
iInventoryTransactableFlag,
iAssembleToOrder,
iSerializableItemFlag
LIMIT COMMIT_SIZE;
IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
(iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
ROLLBACK;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
BEGIN -- bulk update
FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
UPDATE CZ_PS_NODES SET
DEVL_PROJECT_ID= DECODE(NOUPDATE_DEVL_PROJECT_ID,0,iDEVLPROJECTID(j),DEVL_PROJECT_ID),
FROM_POPULATOR_ID= DECODE(NOUPDATE_FROM_POPULATOR_ID,0,iFROMPOPULATORID(j),FROM_POPULATOR_ID),
PROPERTY_BACKPTR= DECODE(NOUPDATE_PROPERTY_BACKPTR,0,iPROPERTYBACKPTR(j),PROPERTY_BACKPTR),
ITEM_TYPE_BACKPTR= DECODE(NOUPDATE_ITEM_TYPE_BACKPTR,0,iITEMTYPEBACKPTR(j),ITEM_TYPE_BACKPTR),
INTL_TEXT_ID= DECODE(NOUPDATE_INTL_TEXT_ID,0,iINTLTEXTID(j),INTL_TEXT_ID),
SUB_CONS_ID= DECODE(NOUPDATE_SUB_CONS_ID,0,iSUBCONSID(j),SUB_CONS_ID),
ITEM_ID= DECODE(NOUPDATE_ITEM_ID,0,iITEMID(j),ITEM_ID),
NAME= DECODE(NOUPDATE_NAME,0,iNAME(j),NAME),
RESOURCE_FLAG= DECODE(NOUPDATE_RESOURCE_FLAG,0,iRESOURCEFLAG(j),RESOURCE_FLAG),
INITIAL_VALUE= DECODE(NOUPDATE_INITIAL_VALUE,0,iINITIALVALUE(j),INITIAL_VALUE),
initial_num_value= DECODE(NOUPDATE_initial_num_value,0,iINITIALNUMVALUE(j),initial_num_value),
PARENT_ID=DECODE(NOUPDATE_PARENT_ID,0,DECODE(iPLANLEVEL(j),0,PARENT_ID,iPARENTID(j)),PARENT_ID),
MINIMUM= DECODE(NOUPDATE_MINIMUM,0,iMINIMUM(j),MINIMUM),
MAXIMUM= DECODE(NOUPDATE_MAXIMUM,0,iMAXIMUM(j),MAXIMUM),
PS_NODE_TYPE= DECODE(NOUPDATE_PS_NODE_TYPE,0,iPSNODETYPE(j),PS_NODE_TYPE),
FEATURE_TYPE= DECODE(NOUPDATE_FEATURE_TYPE,0,iFEATURETYPE(j),FEATURE_TYPE),
PRODUCT_FLAG= DECODE(NOUPDATE_PRODUCT_FLAG,0,iPRODUCTFLAG(j),PRODUCT_FLAG),
REFERENCE_ID= DECODE(NOUPDATE_REFERENCE_ID,0,iREFERENCEID(j),REFERENCE_ID),
MULTI_CONFIG_FLAG= DECODE(NOUPDATE_MULTI_CONFIG_FLAG,0,iMULTICONFIGFLAG(j),MULTI_CONFIG_FLAG),
ORDER_SEQ_FLAG= DECODE(NOUPDATE_ORDER_SEQ_FLAG,0,iORDERSEQFLAG(j),ORDER_SEQ_FLAG),
SYSTEM_NODE_FLAG= DECODE(NOUPDATE_SYSTEM_NODE_FLAG,0,iSYSTEMNODEFLAG(j),SYSTEM_NODE_FLAG),
TREE_SEQ= DECODE(NOUPDATE_TREE_SEQ,0,iTREESEQ(j),TREE_SEQ),
COUNTED_OPTIONS_FLAG= DECODE(NOUPDATE_COUNTED_OPTIONS_FLAG,0,iCOUNTEDOPTIONSFLAG(j),COUNTED_OPTIONS_FLAG),
UI_OMIT= DECODE(NOUPDATE_UI_OMIT,0,iUIOMIT(j),UI_OMIT),
UI_SECTION= DECODE(NOUPDATE_UI_SECTION,0,iUISECTION(j),UI_SECTION),
BOM_TREATMENT= DECODE(NOUPDATE_BOM_TREATMENT,0,iBOMTREATMENT(j),BOM_TREATMENT),
ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,iORIGSYSREF(j),ORIG_SYS_REF),
CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,iCHECKOUTUSER(j),CHECKOUT_USER),
DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,iDELETEDFLAG(j),DELETED_FLAG),
USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,iUSERNUM01(j),USER_NUM01),
USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,iUSERNUM02(j),USER_NUM02),
USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,iUSERNUM03(j),USER_NUM03),
USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,iUSERNUM04(j),USER_NUM04),
USER_STR01= DECODE(NOUPDATE_USER_STR01,0,iUSERSTR01(j),USER_STR01),
USER_STR02= DECODE(NOUPDATE_USER_STR02,0,iUSERSTR02(j),USER_STR02),
USER_STR03= DECODE(NOUPDATE_USER_STR03,0,iUSERSTR03(j),USER_STR03),
USER_STR04= DECODE(NOUPDATE_USER_STR04,0,iUSERSTR04(j),USER_STR04),
--CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
EFFECTIVE_FROM= DECODE(NOUPDATE_EFF_FROM,0,iEFFECTIVEFROM(j),EFFECTIVE_FROM),
EFFECTIVE_UNTIL= DECODE(NOUPDATE_EFF_TO,0,iEFFECTIVEUNTIL(j),EFFECTIVE_UNTIL),
--CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,-UID,CREATED_BY),
LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
--EFFECTIVE_USAGE_MASK= DECODE(NOUPDATE_EFF_MASK,0,iEFFECTIVEUSAGEMASK(j),EFFECTIVE_USAGE_MASK),
SO_ITEM_TYPE_CODE= DECODE(NOUPDATE_SO_ITEM_TYPE_CODE,0,iSOITEMTYPECODE(j),SO_ITEM_TYPE_CODE),
MINIMUM_SELECTED= DECODE(NOUPDATE_MINIMUM_SELECTED,0,iMINIMUMSELECTED(j),MINIMUM_SELECTED),
MAXIMUM_SELECTED= DECODE(NOUPDATE_MAXIMUM_SELECTED,0,iMAXIMUMSELECTED(j),MAXIMUM_SELECTED),
BOM_REQUIRED_FLAG= DECODE(NOUPDATE_BOM_REQUIRED,0,iBOMREQUIRED(j),BOM_REQUIRED_FLAG),
COMPONENT_SEQUENCE_ID= DECODE(NOUPDATE_COMPONENT_SEQUENCE_ID,0,iCOMPONENTSEQUENCEID(j),COMPONENT_SEQUENCE_ID),
DECIMAL_QTY_FLAG= DECODE(NOUPDATE_DECIMAL_QTY_FLAG,0,iDECIMALQTYFLAG(j),DECIMAL_QTY_FLAG),
QUOTEABLE_FLAG= DECODE(NOUPDATE_QUOTEABLE_FLAG,0,iQUOTEABLEFLAG(j),QUOTEABLE_FLAG),
PRIMARY_UOM_CODE= DECODE(NOUPDATE_PRIMARY_UOM_CODE,0,iPRIMARYUOMCODE(j),PRIMARY_UOM_CODE),
BOM_SORT_ORDER= DECODE(NOUPDATE_BOM_SORT_ORDER,0,iBOMSORTORDER(j),BOM_SORT_ORDER),
COMPONENT_SEQUENCE_PATH=DECODE(NOUPDATE_SEQUENCE_PATH,0,iCOMPONENTSEQUENCEPATH(j),COMPONENT_SEQUENCE_PATH),
IB_TRACKABLE= DECODE(NOUPDATE_IB_TRACKABLE,0,iIBTRACKABLE(j),IB_TRACKABLE),
SRC_APPLICATION_ID= iSRCAPPLICATIONID(j),
DISPLAY_IN_SUMMARY_FLAG=DECODE(NOUPDATE_DSPLY_SMRY_FLG,0,iDisplayInSummaryFlag(j),DISPLAY_IN_SUMMARY_FLAG),
IB_LINK_ITEM_FLAG=DECODE(NOUPDATE_IBLINKITEM_FLG,0,iIBLinkItemFlag(j),IB_LINK_ITEM_FLAG),
INSTANTIABLE_FLAG=DECODE(NOUPDATE_INSTANTIABLE_FLAG,0,iINSTANTIABLEFLAG(j),INSTANTIABLE_FLAG),
SHIPPABLE_ITEM_FLAG = DECODE(NOUPDATE_SHIPPABLE_ITEM_FLAG,0,iShippableItemFlag(j), SHIPPABLE_ITEM_FLAG),
INVENTORY_TRANSACTABLE_FLAG = DECODE(NOUPDATE_INV_TXN_FLAG, 0, iInventoryTransactableFlag(j), INVENTORY_TRANSACTABLE_FLAG),
ASSEMBLE_TO_ORDER_FLAG = DECODE(NOUPDATE_ASM_TO_ORDER_FLAG, 0, iAssembleToOrder(j), ASSEMBLE_TO_ORDER_FLAG),
SERIALIZABLE_ITEM_FLAG = DECODE(NOUPDATE_SERIAL_ITEM_FLAG, 0, iSerializableItemFlag(j), SERIALIZABLE_ITEM_FLAG)
WHERE PS_NODE_ID=iPSNODEID(j);
nUpdateCount:= nUpdateCount + SQL%ROWCOUNT;
BEGIN -- bulk update
FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='OK'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
ROLLBACK; -- need to insert row by row to log error messages
EXCEPTION -- bulk update
WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
RAISE;
IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
(iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
ROLLBACK;
BEGIN -- single row update
UPDATE CZ_PS_NODES SET
DEVL_PROJECT_ID= DECODE(NOUPDATE_DEVL_PROJECT_ID,0,iDEVLPROJECTID(j),DEVL_PROJECT_ID),
FROM_POPULATOR_ID= DECODE(NOUPDATE_FROM_POPULATOR_ID,0,iFROMPOPULATORID(j),FROM_POPULATOR_ID),
PROPERTY_BACKPTR= DECODE(NOUPDATE_PROPERTY_BACKPTR,0,iPROPERTYBACKPTR(j),PROPERTY_BACKPTR),
ITEM_TYPE_BACKPTR= DECODE(NOUPDATE_ITEM_TYPE_BACKPTR,0,iITEMTYPEBACKPTR(j),ITEM_TYPE_BACKPTR),
INTL_TEXT_ID= DECODE(NOUPDATE_INTL_TEXT_ID,0,iINTLTEXTID(j),INTL_TEXT_ID),
SUB_CONS_ID= DECODE(NOUPDATE_SUB_CONS_ID,0,iSUBCONSID(j),SUB_CONS_ID),
ITEM_ID= DECODE(NOUPDATE_ITEM_ID,0,iITEMID(j),ITEM_ID),
NAME= DECODE(NOUPDATE_NAME,0,iNAME(j),NAME),
RESOURCE_FLAG= DECODE(NOUPDATE_RESOURCE_FLAG,0,iRESOURCEFLAG(j),RESOURCE_FLAG),
INITIAL_VALUE= DECODE(NOUPDATE_INITIAL_VALUE,0,iINITIALVALUE(j),INITIAL_VALUE),
initial_num_value= DECODE(NOUPDATE_initial_num_value,0,iINITIALNUMVALUE(j),initial_num_value),
PARENT_ID=DECODE(NOUPDATE_PARENT_ID,0,DECODE(iPLANLEVEL(j),0,PARENT_ID,iPARENTID(j)),PARENT_ID),
MINIMUM= DECODE(NOUPDATE_MINIMUM,0,iMINIMUM(j),MINIMUM),
MAXIMUM= DECODE(NOUPDATE_MAXIMUM,0,iMAXIMUM(j),MAXIMUM),
PS_NODE_TYPE= DECODE(NOUPDATE_PS_NODE_TYPE,0,iPSNODETYPE(j),PS_NODE_TYPE),
FEATURE_TYPE= DECODE(NOUPDATE_FEATURE_TYPE,0,iFEATURETYPE(j),FEATURE_TYPE),
PRODUCT_FLAG= DECODE(NOUPDATE_PRODUCT_FLAG,0,iPRODUCTFLAG(j),PRODUCT_FLAG),
REFERENCE_ID= DECODE(NOUPDATE_REFERENCE_ID,0,iREFERENCEID(j),REFERENCE_ID),
MULTI_CONFIG_FLAG= DECODE(NOUPDATE_MULTI_CONFIG_FLAG,0,iMULTICONFIGFLAG(j),MULTI_CONFIG_FLAG),
ORDER_SEQ_FLAG= DECODE(NOUPDATE_ORDER_SEQ_FLAG,0,iORDERSEQFLAG(j),ORDER_SEQ_FLAG),
SYSTEM_NODE_FLAG= DECODE(NOUPDATE_SYSTEM_NODE_FLAG,0,iSYSTEMNODEFLAG(j),SYSTEM_NODE_FLAG),
TREE_SEQ= DECODE(NOUPDATE_TREE_SEQ,0,iTREESEQ(j),TREE_SEQ),
COUNTED_OPTIONS_FLAG= DECODE(NOUPDATE_COUNTED_OPTIONS_FLAG,0,iCOUNTEDOPTIONSFLAG(j),COUNTED_OPTIONS_FLAG),
UI_OMIT= DECODE(NOUPDATE_UI_OMIT,0,iUIOMIT(j),UI_OMIT),
UI_SECTION= DECODE(NOUPDATE_UI_SECTION,0,iUISECTION(j),UI_SECTION),
BOM_TREATMENT= DECODE(NOUPDATE_BOM_TREATMENT,0,iBOMTREATMENT(j),BOM_TREATMENT),
ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,iORIGSYSREF(j),ORIG_SYS_REF),
CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,iCHECKOUTUSER(j),CHECKOUT_USER),
DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,iDELETEDFLAG(j),DELETED_FLAG),
USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,iUSERNUM01(j),USER_NUM01),
USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,iUSERNUM02(j),USER_NUM02),
USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,iUSERNUM03(j),USER_NUM03),
USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,iUSERNUM04(j),USER_NUM04),
USER_STR01= DECODE(NOUPDATE_USER_STR01,0,iUSERSTR01(j),USER_STR01),
USER_STR02= DECODE(NOUPDATE_USER_STR02,0,iUSERSTR02(j),USER_STR02),
USER_STR03= DECODE(NOUPDATE_USER_STR03,0,iUSERSTR03(j),USER_STR03),
USER_STR04= DECODE(NOUPDATE_USER_STR04,0,iUSERSTR04(j),USER_STR04),
--CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
EFFECTIVE_FROM= DECODE(NOUPDATE_EFF_FROM,0,iEFFECTIVEFROM(j),EFFECTIVE_FROM),
EFFECTIVE_UNTIL= DECODE(NOUPDATE_EFF_TO,0,iEFFECTIVEUNTIL(j),EFFECTIVE_UNTIL),
--CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,-UID,CREATED_BY),
LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
--EFFECTIVE_USAGE_MASK= DECODE(NOUPDATE_EFF_MASK,0,iEFFECTIVEUSAGEMASK(j),EFFECTIVE_USAGE_MASK),
SO_ITEM_TYPE_CODE= DECODE(NOUPDATE_SO_ITEM_TYPE_CODE,0,iSOITEMTYPECODE(j),SO_ITEM_TYPE_CODE),
MINIMUM_SELECTED= DECODE(NOUPDATE_MINIMUM_SELECTED,0,iMINIMUMSELECTED(j),MINIMUM_SELECTED),
MAXIMUM_SELECTED= DECODE(NOUPDATE_MAXIMUM_SELECTED,0,iMAXIMUMSELECTED(j),MAXIMUM_SELECTED),
BOM_REQUIRED_FLAG= DECODE(NOUPDATE_BOM_REQUIRED,0,iBOMREQUIRED(j),BOM_REQUIRED_FLAG),
COMPONENT_SEQUENCE_ID= DECODE(NOUPDATE_COMPONENT_SEQUENCE_ID,0,iCOMPONENTSEQUENCEID(j),COMPONENT_SEQUENCE_ID),
DECIMAL_QTY_FLAG= DECODE(NOUPDATE_DECIMAL_QTY_FLAG,0,iDECIMALQTYFLAG(j),DECIMAL_QTY_FLAG),
QUOTEABLE_FLAG= DECODE(NOUPDATE_QUOTEABLE_FLAG,0,iQUOTEABLEFLAG(j),QUOTEABLE_FLAG),
PRIMARY_UOM_CODE= DECODE(NOUPDATE_PRIMARY_UOM_CODE,0,iPRIMARYUOMCODE(j),PRIMARY_UOM_CODE),
BOM_SORT_ORDER= DECODE(NOUPDATE_BOM_SORT_ORDER,0,iBOMSORTORDER(j),BOM_SORT_ORDER),
COMPONENT_SEQUENCE_PATH=DECODE(NOUPDATE_SEQUENCE_PATH,0,iCOMPONENTSEQUENCEPATH(j),COMPONENT_SEQUENCE_PATH),
IB_TRACKABLE= DECODE(NOUPDATE_IB_TRACKABLE,0,iIBTRACKABLE(j),IB_TRACKABLE),
SRC_APPLICATION_ID= iSRCAPPLICATIONID(j),
DISPLAY_IN_SUMMARY_FLAG=DECODE(NOUPDATE_DSPLY_SMRY_FLG,0,iDisplayInSummaryFlag(j),DISPLAY_IN_SUMMARY_FLAG),
IB_LINK_ITEM_FLAG=DECODE(NOUPDATE_IBLINKITEM_FLG,0,iIBLinkItemFlag(j),IB_LINK_ITEM_FLAG),
INSTANTIABLE_FLAG=DECODE(NOUPDATE_INSTANTIABLE_FLAG,0,iINSTANTIABLEFLAG(j),INSTANTIABLE_FLAG),
SHIPPABLE_ITEM_FLAG = DECODE(NOUPDATE_SHIPPABLE_ITEM_FLAG,0,iShippableItemFlag(j), SHIPPABLE_ITEM_FLAG),
INVENTORY_TRANSACTABLE_FLAG = DECODE(NOUPDATE_INV_TXN_FLAG, 0, iInventoryTransactableFlag(j), INVENTORY_TRANSACTABLE_FLAG),
ASSEMBLE_TO_ORDER_FLAG = DECODE(NOUPDATE_ASM_TO_ORDER_FLAG, 0, iAssembleToOrder(j), ASSEMBLE_TO_ORDER_FLAG),
SERIALIZABLE_ITEM_FLAG = DECODE(NOUPDATE_SERIAL_ITEM_FLAG, 0, iSerializableItemFlag(j), SERIALIZABLE_ITEM_FLAG)
WHERE PS_NODE_ID=iPSNODEID(j);
nUpdateCount:= nUpdateCount + 1;
UPDATE CZ_IMP_PS_NODES -- single row update
SET REC_STATUS='OK'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
EXCEPTION -- single row update
WHEN OTHERS THEN
FAILED:=FAILED +1;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
AND DISPOSITION='M';
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=p_model_id
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
END; -- single row update
END; -- bulk update
END LOOP; -- bulk fetch for update
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='OK'
WHERE DISPOSITION='N' and run_id = inRUN_ID
AND devl_project_id = p_model_id;
END update_ps_nodes;
PROCEDURE delete_ps_nodes(p_model_id IN NUMBER, x_retcode OUT NOCOPY NUMBER)
IS
l_ps_node_id tPsNodeId;
SELECT ps_node_id, ps_node_type
FROM cz_ps_nodes a
WHERE deleted_flag = '0'
AND devl_project_id = p_model_id
AND ps_node_type IN (cnReference, cnConnector, cnComponent)
AND NOT EXISTS (SELECT NULL FROM cz_imp_ps_nodes
WHERE orig_sys_ref = a.orig_sys_ref
AND devl_project_id = p_model_id
AND src_application_id = a.src_application_id
AND run_id=inRun_Id);
UPDATE cz_ps_nodes a
SET deleted_flag = '1'
WHERE devl_project_id = p_model_id
AND NOT EXISTS (SELECT NULL FROM cz_imp_ps_nodes
WHERE orig_sys_ref = a.orig_sys_ref
AND devl_project_id = p_model_id
AND src_application_id = a.src_application_id
AND run_id=inRun_Id);
cz_refs.delete_Node(l_ps_node_id(i),l_ps_node_type(i), p_out_err, '1');
END delete_ps_nodes;
NOUPDATE_PS_NODE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PS_NODE_ID',inXFR_GROUP);
NOUPDATE_DEVL_PROJECT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DEVL_PROJECT_ID',inXFR_GROUP);
NOUPDATE_FROM_POPULATOR_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','FROM_POPULATOR_ID',inXFR_GROUP);
NOUPDATE_PROPERTY_BACKPTR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PROPERTY_BACKPTR',inXFR_GROUP);
NOUPDATE_ITEM_TYPE_BACKPTR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ITEM_TYPE_BACKPTR',inXFR_GROUP);
NOUPDATE_INTL_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INTL_TEXT_ID',inXFR_GROUP);
NOUPDATE_SUB_CONS_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SUB_CONS_ID',inXFR_GROUP);
NOUPDATE_ITEM_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ITEM_ID',inXFR_GROUP);
NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','NAME',inXFR_GROUP);
NOUPDATE_RESOURCE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','RESOURCE_FLAG',inXFR_GROUP);
NOUPDATE_INITIAL_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INITIAL_VALUE',inXFR_GROUP);
NOUPDATE_initial_num_value := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','initial_num_value',inXFR_GROUP);
NOUPDATE_PARENT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PARENT_ID',inXFR_GROUP);
NOUPDATE_MINIMUM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MINIMUM',inXFR_GROUP);
NOUPDATE_MAXIMUM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MAXIMUM',inXFR_GROUP);
NOUPDATE_PS_NODE_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PS_NODE_TYPE',inXFR_GROUP);
NOUPDATE_FEATURE_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','FEATURE_TYPE',inXFR_GROUP);
NOUPDATE_PRODUCT_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PRODUCT_FLAG',inXFR_GROUP);
NOUPDATE_REFERENCE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','REFERENCE_ID',inXFR_GROUP);
NOUPDATE_MULTI_CONFIG_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MULTI_CONFIG_FLAG',inXFR_GROUP);
NOUPDATE_ORDER_SEQ_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ORDER_SEQ_FLAG',inXFR_GROUP);
NOUPDATE_SYSTEM_NODE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SYSTEM_NODE_FLAG',inXFR_GROUP);
NOUPDATE_TREE_SEQ := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','TREE_SEQ',inXFR_GROUP);
NOUPDATE_COUNTED_OPTIONS_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COUNTED_OPTIONS_FLAG',inXFR_GROUP);
NOUPDATE_UI_OMIT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','UI_OMIT',inXFR_GROUP);
NOUPDATE_UI_SECTION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','UI_SECTION',inXFR_GROUP);
NOUPDATE_BOM_TREATMENT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_TREATMENT',inXFR_GROUP);
NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ORIG_SYS_REF',inXFR_GROUP);
NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CHECKOUT_USER',inXFR_GROUP);
NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DELETED_FLAG',inXFR_GROUP);
NOUPDATE_EFF_FROM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_FROM',inXFR_GROUP);
NOUPDATE_EFF_TO := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_UNTIL',inXFR_GROUP);
NOUPDATE_EFF_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_USAGE_MASK',inXFR_GROUP);
NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR01',inXFR_GROUP);
NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR02',inXFR_GROUP);
NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR03',inXFR_GROUP);
NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR04',inXFR_GROUP);
NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM01',inXFR_GROUP);
NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM02',inXFR_GROUP);
NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM03',inXFR_GROUP);
NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM04',inXFR_GROUP);
NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CREATION_DATE',inXFR_GROUP);
NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','LAST_UPDATE_DATE',inXFR_GROUP);
NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CREATED_BY',inXFR_GROUP);
NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','LAST_UPDATED_BY',inXFR_GROUP);
NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SECURITY_MASK',inXFR_GROUP);
NOUPDATE_SO_ITEM_TYPE_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SO_ITEM_TYPE_CODE',inXFR_GROUP);
NOUPDATE_MINIMUM_SELECTED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MINIMUM_SELECTED',inXFR_GROUP);
NOUPDATE_MAXIMUM_SELECTED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MAXIMUM_SELECTED',inXFR_GROUP);
NOUPDATE_BOM_REQUIRED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_REQUIRED_FLAG',inXFR_GROUP);
NOUPDATE_COMPONENT_SEQUENCE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COMPONENT_SEQUENCE_ID',inXFR_GROUP);
NOUPDATE_DECIMAL_QTY_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DECIMAL_QTY_FLAG',inXFR_GROUP);
NOUPDATE_QUOTEABLE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','QUOTEABLE_FLAG',inXFR_GROUP);
NOUPDATE_PRIMARY_UOM_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PRIMARY_UOM_CODE',inXFR_GROUP);
NOUPDATE_BOM_SORT_ORDER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_SORT_ORDER',inXFR_GROUP);
NOUPDATE_SEQUENCE_PATH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COMPONENT_SEQUENCE_PATH',inXFR_GROUP);
NOUPDATE_IB_TRACKABLE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','IB_TRACKABLE',inXFR_GROUP);
NOUPDATE_DSPLY_SMRY_FLG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DISPLAY_IN_SUMMARY_FLAG',inXFR_GROUP);
NOUPDATE_IBLINKITEM_FLG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','IB_LINK_ITEM_FLAG',inXFR_GROUP);
NOUPDATE_INSTANTIABLE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INSTANTIABLE_FLAG',inXFR_GROUP);
IF(inXFR_GROUP = 'IMPORT')THEN NOUPDATE_INSTANTIABLE_FLAG := 1; END IF;
NOUPDATE_SHIPPABLE_ITEM_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SHIPPABLE_ITEM_FLAG',inXFR_GROUP);
NOUPDATE_INV_TXN_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INVENTORY_TRANSACTABLE_FLAG',inXFR_GROUP);
NOUPDATE_ASM_TO_ORDER_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ASSEMBLE_TO_ORDER_FLAG ',inXFR_GROUP);
NOUPDATE_SERIAL_ITEM_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SERIALIZABLE_ITEM_FLAG',inXFR_GROUP);
insert_ps_nodes(l_c1_prj_id_tbl(i), l_retcode);
update_ps_nodes(l_c1_prj_id_tbl(i));
delete_ps_nodes(l_c1_prj_id_tbl(i),l_retcode);
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=l_c1_prj_id_tbl(i)
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
insert_ps_nodes(l_c2_prj_id_tbl(i), l_retcode);
update_ps_nodes(l_c2_prj_id_tbl(i));
delete_ps_nodes(l_c2_prj_id_tbl(i),l_retcode);
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=l_c2_prj_id_tbl(i)
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
GOTO UPDATE_IMP_TABLE;
insert_ps_nodes(l_c3_prj_id_tbl(i), l_retcode);
update_ps_nodes(l_c3_prj_id_tbl(i));
delete_ps_nodes(l_c3_prj_id_tbl(i),l_retcode);
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=l_c3_prj_id_tbl(i)
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
<>
-- Now update the imp tables for failed models of C1
IF l_c1_prj_id_tbl.COUNT > 0 THEN
FOR i IN l_c1_prj_id_tbl.FIRST..l_c1_prj_id_tbl.LAST LOOP
IF l_c1_rec_status_tbl(i)='ERR' THEN
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=l_c1_prj_id_tbl(i)
AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_DEVL_PROJECT
SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=l_c1_prj_id_tbl(i)
AND RUN_ID=inRUN_ID
AND DISPOSITION=l_c1_dis_tbl(i);
DELETE FROM cz_devl_projects
WHERE devl_project_id = l_c1_prj_id_tbl(i);
DELETE FROM cz_rp_entries
WHERE object_id = l_c1_prj_id_tbl(i)
AND object_type = 'PRJ';
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS=DECODE(l_c2_rec_status_tbl(i),'SKP','PASS',l_c2_rec_status_tbl(i))
WHERE DEVL_PROJECT_ID=l_c2_prj_id_tbl(i)
AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_DEVL_PROJECT
SET REC_STATUS=DECODE(l_c2_rec_status_tbl(i),'SKP','PASS',l_c2_rec_status_tbl(i))
WHERE DEVL_PROJECT_ID=l_c2_prj_id_tbl(i)
AND RUN_ID=inRUN_ID
AND DISPOSITION=l_c2_dis_tbl(i);
DELETE FROM cz_devl_projects
WHERE devl_project_id = l_c2_prj_id_tbl(i);
DELETE FROM cz_rp_entries
WHERE object_id = l_c2_prj_id_tbl(i)
AND object_type = 'PRJ';
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS=DECODE(l_c3_rec_status_tbl(i),'SKP','PASS',l_c3_rec_status_tbl(i))
WHERE DEVL_PROJECT_ID=l_c3_prj_id_tbl(i)
AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_DEVL_PROJECT
SET REC_STATUS=DECODE(l_C3_rec_status_tbl(i),'SKP','PASS',l_c3_rec_status_tbl(i))
WHERE DEVL_PROJECT_ID=l_c3_prj_id_tbl(i)
AND RUN_ID=inRUN_ID
AND DISPOSITION=l_c3_dis_tbl(i);
DELETE FROM cz_devl_projects
WHERE devl_project_id = l_c3_prj_id_tbl(i);
DELETE FROM cz_rp_entries
WHERE object_id = l_c3_prj_id_tbl(i)
AND object_type = 'PRJ';
insert_ps_nodes(l_c4_prj_id_tbl(i), l_retcode);
update_ps_nodes(l_c4_prj_id_tbl(i));
delete_ps_nodes(l_c4_prj_id_tbl(i),l_retcode);
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=l_c4_prj_id_tbl(i)
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE DEVL_PROJECT_ID=l_c4_prj_id_tbl(i)
AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_DEVL_PROJECT
SET REC_STATUS='PASS'
WHERE DEVL_PROJECT_ID=l_c4_prj_id_tbl(i)
AND RUN_ID=inRUN_ID
AND DISPOSITION=l_c4_dis_tbl(i);
DELETE FROM cz_devl_projects
WHERE devl_project_id = l_c4_prj_id_tbl(i);
DELETE FROM cz_rp_entries
WHERE object_id = l_c4_prj_id_tbl(i)
AND object_type = 'PRJ';
UPDATE CZ_IMP_PS_NODES
SET REC_STATUS='ERR'
WHERE PS_NODE_ID=l_PsNodeId(i)
AND RUN_ID=inRUN_ID
AND DISPOSITION=l_dis(i);
SELECT name INTO l_model_name
FROM cz_imp_devl_project
WHERE devl_project_id=l_DevlProjectId(i)
AND deleted_flag='0'
AND run_id=inRun_ID
AND rec_status='OK'
AND rownum <2;
cz_refs.update_node_depth(NULL) ;
FOR c_model IN (SELECT devl_project_id FROM cz_imp_devl_project
WHERE run_id = inRUN_ID
AND rec_status = 'OK')LOOP
cz_refs.populate_component_id(c_model.devl_project_id);
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_PS_NODES
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;