APPS.CZ_IMP_ALL SQL Statements

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

Line: 13

  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 nEnableTrace FROM CZ_DB_SETTINGS
  WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;

Line: 32

  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 nEnableLog FROM CZ_DB_SETTINGS
  WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;

Line: 146

  SELECT server_local_id INTO nServerId
    FROM cz_servers
   WHERE import_enabled = v_enabled;

Line: 162

SELECT organization_id, top_item_id, explosion_type, NVL(copy_addl_child_models, '0'), model_ps_node_id
BULK COLLECT INTO
orgId,topItemId,explType,copyChildModel,modelId
FROM cz_xfr_project_bills
WHERE deleted_flag = '0'
   AND source_server = nServerId;

Line: 210

    SELECT DST_TABLE,SRC_TABLE FROM CZ_XFR_TABLES
    WHERE XFR_GROUP='GENERIC' AND DISABLED='0'
    ORDER BY ORDER_SEQ;

Line: 226

         INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
         SELECT genRun_ID,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
         (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=genRun_ID);

Line: 229

         UPDATE CZ_XFR_RUN_INFOS SET
           STARTED=SYSDATE,LAST_ACTIVITY=SYSDATE,COMPLETED='0'
         WHERE RUN_ID=genRun_ID;

Line: 249

         SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO genRun_ID FROM DUAL;

Line: 250

         INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
         SELECT genRun_ID,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
         (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=genRun_ID);

Line: 253

         UPDATE CZ_XFR_RUN_INFOS SET
           STARTED=SYSDATE,LAST_ACTIVITY=SYSDATE,COMPLETED='0'
         WHERE RUN_ID=genRun_ID;

Line: 282

        SELECT VALUE INTO nCommit_size FROM CZ_DB_SETTINGS
        WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;

Line: 297

        SELECT VALUE INTO nMax_err FROM CZ_DB_SETTINGS
        WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;

Line: 336

       UPDATE CZ_XFR_RUN_INFOS SET
        LAST_ACTIVITY=SYSDATE,
        COMPLETED='1'
       WHERE RUN_ID=genRun_ID;

Line: 348

         SELECT VALUE INTO bAutoCreateUsers FROM CZ_DB_SETTINGS
         WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;

Line: 391

  Inserts  PLS_INTEGER;

Line: 392

  Updates  PLS_INTEGER;

Line: 403

                                  Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);

Line: 406

                                     Inserts, Updates, x_failed, Dups,NoChange, inXFR_GROUP);

Line: 409

                              Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 412

                                       Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 415

                              Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 418

                                   Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 421

                              Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 424

                            Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 427

                                  Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 430

                                       Inserts, Updates, x_failed, Dups, NoChange, inXFR_GROUP);

Line: 435

                                      Inserts, Updates, x_failed, Dups, NoChange, inXFR_GROUP, p_rp_folder_id,l_CONFIG_ENGINE_TYPE); -- sselahi rpf

Line: 438

                               Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 441

                                     Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 444

                                          Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);

Line: 447

                                Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);

Line: 450

                                         Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 453

                               Inserts, Updates, x_failed, Dups,NoChange, inXFR_GROUP, p_rp_folder_id);

Line: 456

                                 Inserts, Updates, x_failed, Dups, inXFR_GROUP);

Line: 476

    SELECT substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)
    FROM CZ_PRICE_GROUPS
    WHERE to_number(substr(ORIG_SYS_REF,instr(ORIG_SYS_REF,'.')+1))=nPriceGroupID
    AND instr(ORIG_SYS_REF,'.')<>0;

Line: 481

    SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
    WHERE ORIG_SYS_REF IS NULL FOR UPDATE;

Line: 502

        UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=sOrigSysRef WHERE CURRENT OF C_SET_ID;

Line: 516

    SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
    WHERE instr(ORIG_SYS_REF,'.')<>0 AND
          substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)=sOrigSysRef;

Line: 520

    SELECT * FROM CZ_PRICE_GROUPS
    WHERE ORIG_SYS_REF IS NOT NULL AND instr(ORIG_SYS_REF,'.')=0
    FOR UPDATE;

Line: 540

    select price_list_id from cz_xfr_price_lists
    where deleted_flag='0';

Line: 543

     select desc_text from cz_price_groups
     where orig_sys_ref=to_char(nPriceListId)
     and deleted_flag='0';

Line: 555

    select disabled into sDisabled
    from cz_xfr_tables where dst_table='CZ_PRICE_GROUPS' and xfr_group='IMPORT';

Line: 573

          update cz_xfr_price_lists set
           description=sDescription,
           last_import_run_id=inRUN_ID,
           last_import_date=sysdate
          where
           price_list_id=nPriceListId;

Line: 580

          update cz_xfr_price_lists set
           source_price_deleted='1'
          where
           price_list_id=nPriceListId;

Line: 602

   SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
   WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;

Line: 621

        UPDATE CZ_PRICE_GROUPS SET
          DESC_TEXT=P_GET_HOST.DESC_TEXT,
          CURRENCY=P_GET_HOST.CURRENCY,
          NAME=P_GET_HOST.NAME,
          USER_NUM01=P_GET_HOST.USER_NUM01,
          USER_NUM02=P_GET_HOST.USER_NUM02,
          USER_NUM03=P_GET_HOST.USER_NUM03,
          USER_NUM04=P_GET_HOST.USER_NUM04,
          USER_STR01=P_GET_HOST.USER_STR01,
          USER_STR02=P_GET_HOST.USER_STR02,
          USER_STR03=P_GET_HOST.USER_STR03,
          USER_STR04=P_GET_HOST.USER_STR04,
          CREATION_DATE=P_GET_HOST.CREATION_DATE,
          LAST_UPDATE_DATE=P_GET_HOST.LAST_UPDATE_DATE,
          DELETED_FLAG=P_GET_HOST.DELETED_FLAG,
          CREATED_BY=P_GET_HOST.CREATED_BY,
          LAST_UPDATED_BY=P_GET_HOST.LAST_UPDATED_BY,
          SECURITY_MASK=P_GET_HOST.SECURITY_MASK,
          CHECKOUT_USER=P_GET_HOST.CHECKOUT_USER
       WHERE PRICE_GROUP_ID=nPriceGroupID;

Line: 641

       UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
       WHERE CURRENT OF C_GET_HOST;

Line: 652

          SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;

Line: 654

        INSERT INTO CZ_PRICE_GROUPS
          (PRICE_GROUP_ID,DESC_TEXT,CURRENCY,ORIG_SYS_REF,
           NAME,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
           USER_STR01,USER_STR02,USER_STR03,USER_STR04,DELETED_FLAG,
           SECURITY_MASK,CHECKOUT_USER,
           CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY)
        VALUES
          (nNextValue+nAllocateCounter,P_GET_HOST.DESC_TEXT,P_GET_HOST.CURRENCY,
           P_GET_HOST.ORIG_SYS_REF||'.'||P_GET_HOST.PRICE_GROUP_ID,P_GET_HOST.NAME,
           P_GET_HOST.USER_NUM01,P_GET_HOST.USER_NUM02,
           P_GET_HOST.USER_NUM03,P_GET_HOST.USER_NUM04,
           P_GET_HOST.USER_STR01,P_GET_HOST.USER_STR02,
           P_GET_HOST.USER_STR03,P_GET_HOST.USER_STR04,P_GET_HOST.DELETED_FLAG,
           P_GET_HOST.SECURITY_MASK,
           P_GET_HOST.CHECKOUT_USER,
           SYSDATE,SYSDATE,1,1);

Line: 670

        UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
        WHERE CURRENT OF C_GET_HOST;

Line: 734

  SELECT server_local_id INTO server_id
    FROM cz_servers
   WHERE import_enabled = v_enabled;

Line: 750

 insert into cz_xfr_project_bills (top_item_id,organization_id,deleted_flag,explosion_type,model_ps_node_id,
     copy_addl_child_models,source_server)
 select nTop_ID,nOrg_ID,'0','OPTIONAL',cz_xfr_project_bills_s.NEXTVAL,COPY_CHILD_MODELS,server_id
    from dual where not exists
 (select 1 from cz_xfr_project_bills where organization_id=nOrg_ID and
  top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id);

Line: 756

 update cz_xfr_project_bills set deleted_flag='0',copy_addl_child_models = COPY_CHILD_MODELS
  where organization_id=nOrg_ID and
  top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id;

Line: 778

  SELECT server_local_id INTO server_id
    FROM cz_servers
   WHERE import_enabled = v_enabled;

Line: 794

 update cz_xfr_project_bills set
  deleted_flag=DECODE(UPPER(sState),'0','1','OFF','1','NO','1','DISABLE','1',
   '1','0','ON','0','YES','0','ENABLE','0',DELETED_FLAG)
 where organization_id=nOrg_ID and top_item_id=nTop_ID and explosion_type='OPTIONAL' AND
   source_server = server_id;

Line: 821

  SELECT server_local_id INTO server_id
    FROM cz_servers
   WHERE import_enabled = v_enabled;

Line: 837

 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
 WHERE ORGANIZATION_CODE = sOrg_ID;

Line: 840

 SELECT inventory_item_id INTO nTop_ID
 FROM cz_exv_mtl_system_items
 WHERE bom_item_type = 1
   AND organization_id = nOrg_ID
   AND concatenated_segments = sTop_ID
   AND rownum = 1;

Line: 847

 update cz_xfr_project_bills set
  deleted_flag='1'
 where organization_id=nOrg_ID and top_item_id=nTop_ID
   and source_server = server_id;

Line: 865

   SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
   WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
	AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
	AND B.BOM_ITEM_TYPE=1
	AND A.ORGANIZATION_ID=Org_ID
	AND B.CONCATENATED_SEGMENTS BETWEEN sFrom AND NVL(sTo,sFrom);

Line: 882

 SELECT DECODE(COPY_CHILD_MODELS,'Y','0','N','1','YES','0','NO','1','TRUE','0','FALSE','1','T','0','F','1','1','1','0') into v_copy_child_models from dual;

Line: 884

 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
 WHERE ORGANIZATION_CODE = sOrg_ID;

Line: 938

   SELECT B.INVENTORY_ITEM_ID FROM CZ_EXV_MTL_SYSTEM_ITEMS B
   WHERE B.BOM_ITEM_TYPE=1
    AND B.ORGANIZATION_ID=Org_ID
    AND B.CONCATENATED_SEGMENTS = sFrom ;

Line: 953

 SELECT DECODE(COPY_CHILD_MODELS,'Y','0','N','1','YES','0','NO','1','TRUE','0','FALSE','1','T','0','F','1','1','1','0') into v_copy_child_models from dual;

Line: 955

 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
 WHERE ORGANIZATION_CODE = sOrg_ID;

Line: 1011

  DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||' SET RUN_ID='||inRun_ID||
                 ',REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID IS NULL',DBMS_SQL.NATIVE);

Line: 1025

  DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||
                 ' SET REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID='||inRun_ID,DBMS_SQL.NATIVE);

Line: 1049

	SELECT ORGANIZATION_ID, ASSEMBLY_ITEM_ID
	INTO commonOrgId, commonInvId
	FROM CZ_EXV_BILL_OF_MATERIALS
	WHERE BILL_SEQUENCE_ID in
				(SELECT COMMON_BILL_SEQUENCE_ID FROM CZ_EXV_BILL_OF_MATERIALS
							WHERE ORGANIZATION_ID = nOrg_ID
	   						AND ASSEMBLY_ITEM_ID = nTop_ID);