{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Coversheet","Connect Information","Template","Data Input","Staging & Submission","Errors","Config"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":60,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"row_number_cap","date_system":"1900"}

{"sheet":"Coversheet","index":0,"n_rows":18,"n_cols":3,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Excel To CI Utility"]
[null,null,"The purpose of this workbook is to upload data from Excel into PeopleSoft using the Component Interface to execute business logic for each transaction. This source workbook contains both worksheets and Excel VBA code modules.  The Worksheets can be copied to other workbooks for distribution without copying the code modules."]
[]
[null,"Process:"]
[null,"1)","Connection Information sheet: The information provided on this sheet is required to create a new template or submit data to the database. You will need to specify environment information as well as information in regard to how each transaction should be handled. The Action will be filled in automatically. By default, character fields which are left blank or only contain blanks will not be submitted for update. There is an option to override this setting."]
[]
[null,"2)","Template sheet: Here you create the template that you are going to use. The template is based upon the structure of a Component Interface on the PeopleSoft database."]
[null,null,"2.1. New Template: When prompted, enter the name of your PeopleSoft User ID, password, and the Component Interface you wish to use. The connect information previously provided is used to retrieve the PeopleSoft Component Interface properties. The Component Interface structure is displayed graphically."]
[null,null,"2.2. Manipulate Template: Here input cells are selected for inclusion on the Data Input and Staging & Submisson sheets.  The purpose of each button provided to allow manipulation of the template is more fully described by mousing over the button on the toolbar."]
[null,null,"2.3. New Data Input: This button copies the selected input cells into the Data Input sheet. The Data Input sheet becomes active, and you will be prompted before the structures and data on that sheet are deleted."]
[]
[null,"3)","Data Input sheet: Here you enter data values for submission to the PeopleSoft database."]
[null,null,"3.1. Stage Data for Submission: The data entered on the Data Input sheet is then staged in hierarchical form in preparation for submission to the database."]
[]
[null,"4)","Staging & Submission sheet: The last step is to format the data and submit to the database."]
[null,null,"4.1. Submit Data: This submits the data to the database.  Your PeopleSoft User ID and Password will be required. Each row submitted is marked with the reply from the database which will either be OK, Warning, or Error."]
[null,null,"4.2. Post Results: This posts the status for each row submitted to the database on the Data Input sheet so that data in a status of error can be corrected and resubmitted."]

{"sheet":"Connect Information","index":1,"n_rows":23,"n_cols":5,"rows_kept":23,"cut":false,"hidden":false,"merged":[],"read":"row_number_cap"}
[]
[]
[]
[]
[null,"Environment:","Development"]
[null,"Web Server Machine Name:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Config!A1:B26,2,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,2,FALSE))","v":"empowhr.dev.nfc.usda.gov"},null,"Optional Keys:"]
[null,"Protocol:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,3,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,3,FALSE))","v":"https"}]
[null,"HTTP Port:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,4,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,4,FALSE))","v":443}]
[null,"Portal:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,5,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,5,FALSE))","v":"EMPLOYEE"}]
[null,"PeopleSoft Site Name:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,6,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,6,FALSE))","v":"hr"}]
[null,"Node:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,7,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,7,FALSE))","v":"PT_LOCAL"}]
[null,"Language Code:",{"$f":"IF(ISNA(VLOOKUP(C5 & \"\",Z_ConnectTable,8,FALSE)),\"\", VLOOKUP(C5 & \"\",Z_ConnectTable,8,FALSE))","v":"ENG"}]
[]
[null,"Chunking Factor:","40"]
[null,"Error Threshold:","1"]
[null,"Submit Blanks as Input:"]
[null,"Action:","CREATE"]
[null,"Action:"]
[null,"Submit Options"]
[]
[null,"Allow Blanks:"," "]
[null,"Z_PRES_DOC_075_CI"]
[null,"Submit Options"]

{"sheet":"Template","index":2,"n_rows":17,"n_cols":31,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[]
["Z_PRES_DOC_075_CI"]
[]
[null,null,"Collection","Z_PRES_DOC_075_CI","Z_PRES_DOC_075_CI","Z_PRES_DOC_075_CI","Z_PRES_DOC_075_CI","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PRES_KEYS_VW","Z_PRES_KEYS_VW","Z_PRES_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_KEYS_VW","Z_DTRK_FIELD_VW"]
[null,null,"Property","EMPLID","EMPL_RCD","REVIEW_FROM_DT","EMPL_RCD_0","REVIEW_THRU_DT","RATING_SCALE","REVIEW_RATING","GVT_PI_UPD_IND","YR_ACQUIRED","Z_PAY_PERIOD_NUM_0","Z_OVERRIDE_CODE","DOC_TYPE","DOCUMENT_TYPE_1","RUN_DT_1","BATCH_NUM_1","DATA_KEY1","DATA_KEY1_DEC","DATA_KEY1_DT","DATA_KEY2","DATA_KEY2_DEC","DATA_KEY2_DT","DATA_KEY3","DATA_KEY3_DEC","DATA_KEY3_DT","DATA_KEY4","DATA_KEY4_DEC","DATA_KEY4_DT","GVT_TRK_SEQUENCE"]
[null,null,"Record Type","0-0-0","0-0-0","0-0-0","0-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-1-0","1-1-0","1-1-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-0","1-2-1"]
[null,null,"Field Type","CHARACTER","NUMBER","DATE","NUMBER","DATE","CHARACTER","CHARACTER","CHARACTER","NUMBER","CHARACTER","CHARACTER","CHARACTER","CHARACTER","DATE","NUMBER","CHARACTER","SIGNED_NUMBER","DATE","CHARACTER","SIGNED_NUMBER","DATE","CHARACTER","SIGNED_NUMBER","DATE","CHARACTER","SIGNED_NUMBER","DATE","NUMBER"]
[null,null,"Field Length","11","3","10","3","10","4","1","1","4","2","1","8","8","10","4","18","12.6","10","18","12.6","10","18","12.6","10","18","12.6","10","2"]
[null,null,"Key / Required","Y/R","Y/ ","Y/R","Y/ "," / "," / "," / "," / "," / "," / "," / ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ ","Y/ "]
[null,null,"Sequence","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28"]
["Status","Line No","Record Type","Empl ID","Empl Record","Review Period From","Empl Record","Review Period To","Rating Scale","Review Rating","Transaction Status","Year Acquired","Pay Period Number","Override Code","Document Type","Document Type","Run Date","Batch Number","Key 1 Data - Character","Key 1 Data - Decimal","Key 1 Data - Date","Key 2 Data - Character","Key 2 Data - Decimal","Key 2 Data - Date","Key 3 Data - Character","Key 3 Data - Decimal","Key 3 Data - Date","Key 4 Data - Character","Key 4 Data - Decimal","Key 4 Data - Date","WIP Sequence Number"]
["Template",null,"0-0-0","","","",""]
["Template",null,"1-0-0",null,null,null,null,"","","","","","","",""]
["Template",null,"1-1-0",null,null,null,null,null,null,null,null,null,null,null,null,"","",""]
["Template",null,"1-1-1"]
["Template",null,"1-2-0",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","","","","","","","","","",""]
["Template",null,"1-2-1",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]

{"sheet":"Data Input","index":3,"n_rows":5,"n_cols":8,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
["Z_PRES_DOC_075_CI"]
["Record Type 0-0-0","Record Type 0-0-0","Record Type 0-0-0","Record Type 1-0-0","Record Type 1-0-0","Record Type 1-0-0","Record Type 1-0-0","Record Type 1-0-0"]
["Empl ID","Empl Record","Review Period From","Review Period To","Rating Scale","Review Rating","Year Acquired","Pay Period Number"]
["103440",0,"2020-10-01","2021-10-01","A","1",2021,"21"]

{"sheet":"Staging & Submission","index":4,"n_rows":17,"n_cols":11,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[]
["Z_PRES_DOC_075_CI"]
[null,null,null,"Z_PRES_DOC_075_CI","Z_PRES_DOC_075_CI","Z_PRES_DOC_075_CI","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS","Z_PP_EE_RVW_HIS"]
[null,null,null,"EMPLID","EMPL_RCD","REVIEW_FROM_DT","REVIEW_THRU_DT","RATING_SCALE","REVIEW_RATING","YR_ACQUIRED","Z_PAY_PERIOD_NUM_0"]
[null,null,null,"0-0-0","0-0-0","0-0-0","1-0-0","1-0-0","1-0-0","1-0-0","1-0-0"]
[null,null,null,"CHARACTER","NUMBER","DATE","DATE","CHARACTER","CHARACTER","NUMBER","CHARACTER"]
[null,null,null,"11","3","10","10","4","1","4","2"]
[null,null,null,"Y/R","Y/ ","Y/R"," / "," / "," / "," / "," / "]
[null,null,null,"1","2","3","5","6","7","9","10"]
["Status","Line No","Record Type","Empl ID","Empl Record","Review Period From","Review Period To","Rating Scale","Review Rating","Year Acquired","Pay Period Number"]
["Error",5,"0-0-0","103440",0,"2020-10-01"]
[" ",5,"1-0-0",null,null,null,"2021-10-01","A","3",2021,"21"]
[" ",5,"1-1-0"]
[" ",5,"1-1-1"]
[" ",5,"1-2-0"]
[" ",5,"1-2-1"]
["STOP"]

{"sheet":"Errors","index":5,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[5,"Errors:\n#1 - This 075 Performance Document already exists. (30028,56)\n#2 - Error saving Component Interface. {Z_PRES_DOC_075_CI} (91,37)\n#3 - Error saving Component Interface. {Z_PRES_DOC_075_CI}\n"]

{"sheet":"Config","index":6,"n_rows":24,"n_cols":10,"rows_kept":24,"cut":false,"hidden":true,"merged":[]}
["AG - Production COPY","empusda.copy.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["AG - Production","usda.empowhr.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DHS - Production COPY","empdhs.copy.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DHS - Production","dhs.empowhr.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DOJ - Production COPY","empdoj.copy.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DOJ - Production  ","doj.empowhr.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["OFA - Production COPY","empofa.copy.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["OFA - Production","ofa.empowhr.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["USDAUAT","empusda.cuat.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DHSUAT","empdhs.cuat.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DOJUAT","empdoj.cuat.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["OFAUAT","ofa.cuat.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["USDAQA","empusda.qa.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DHSQA","empdhs.qa.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DOJQA","empdoj.qa.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["OFAQA","ofa.qa.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["USDATEST","empusda.test.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DHSTEST","empdhs.test.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["DOJTEST","empdoj.test.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["OFATEST","ofa.test.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["Training","train.empowhr.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
["Development","empowhr.dev.nfc.usda.gov","https",443,"EMPLOYEE","hr","PT_LOCAL","ENG",10,10]
[]
["CREATE"]