Table Updates/Inserts
This section lists the tables that are being updated by the Import Purchase Requisitions screen and the values inserted into the tables.
Table Name: RQ_HDR
| Column | Value/Source | Comments |
|---|---|---|
| AUTO_GEN_FL | N | |
| BUYER_ASSIGN_DT | From input file (or staging table) if requisition settings has manual buyer assignment (RQ_SETTINGS. S_BUYER_ASSIGN_CD = M.
Else, if BUYER_ID is not null, load system date. Else, null. |
|
| BUYER_ID | From input file (or staging table) if requisition settings has manual buyer assignment (RQ_SETTINGS. S_BUYER_ASSIGN_CD = M).
Also from input file (or staging table) for other types of buyer assignment (RQ_SETTINGS.S_BUYER_ASSIGN_CD = C, V, I, OR P) If RQ_SETTINGS.BUYER_OVRIDE_FL = Y. Else, null. |
|
| COMBINE_RQ_FL | From input file (or staging table).
Else, RQ_SETTINGS. DFLT_COMBINE_RQ_FL |
|
| COMPANY_ID | User’s company ID | |
| ENTR_DTT | Input file (or staging table). Else, System Date & Time. | |
| ENTR_USER_ID | User_ID | |
| EST_SALES_TAX_AMT | Sum of requisition line sales tax total amounts (RQ_LN. EST_SALES_TAX_AMT) | |
| EUR_TO_FUNC_RT | Default value [MU_CRNCY_STATUS. EURO_TO_CRNCY_RT)] for matching transaction currency code.
Else if unavailable, set to 1 |
|
| GSA_FL | From input file (or staging table).
Else, N. |
|
| LAST_CHNG_DT | SYSTEM DATE | |
| MODIFIED_BY | User ID | |
| NEXT_APPRVL_SEQ_NO | 0 | |
| OVR_BUD_FL | RQ_SETTINGS. RQ_BUDG_VAL_FL | |
| PRINTED_FL | N | |
| PROCURE_TYPE_CD | From input file. Else Null. | |
| PROJ_ABBRV_CD | From PROJ for associated PROJ_ID. Else Null if PROJ_ID is null. | |
| PROJ_ID | From input file, Else Null | |
| RATE_GRP_ID | From input file (or staging table)
Else, from multicurrency settings (MU_SETTINGS.DFLT_RT_GRP_ID) Else, null. |
|
| ROWVERSION | 0 | |
| RQ_APPR_PROC_CD | If approvals are required at requisition header level (RQ_SETTINGS. S_APPR_PROC_ASG_CD not N, S_APPRVL_LVL_CD = R), default as follows depending upon the value of RQ_SETTINGS. S_APPR_PROC_ASG_CD.
If "G" (Global), default RQ_SETTINGS. RQ_APPR_PROC_CD. If "M" (Manual), load from input file (if available); else default INVT_PROJ. RQ_APPR_PROC_CD for first requisition line (if not null), else from RQ_SETTINGS. RQ_APPR_PROC_CD. If "P" (Proj/Acct/Org), default from PROJ. RQ_APPR_PROC_CD or ORG_ACCT. RQ_APPR_PROC_CD for first req line. If "I" (Item), default RQ_APPRVL_PROC. RQ_APPR_PROC_CD from row associated with the item type of first req line (for example select value from row with PART_TYPE_FL = Y if req line is a Part, etc). Else null |
Error will be displayed if approval is required but value is not available. |
| RQ_DT | From input file (or staging table)
Else, system date |
|
| RQ_EXPORT_CD | Null. | |
| RQ_EXPORT_DTT | Null. | |
| RQ_ID | From input file (or staging table). | Error will be displayed if req ID is not available. |
| RQ_NOTES | Header Notes | |
| RQ_PROC_FL | N | |
| RQST_EMPL_ID | From input file (or staging table) | |
| RQST_ORG_ID | From input file (or staging table) if available. Else EMPL_LAB_INFO.ORG_ID for matching RQST_EMPL_ID. Else null. | |
| RQST_PHONE_EXT_CD | From input file (or staging table) if available. Else EMPL_PHONE.PHONE_EXT_CD from row with the lowest SEQ_NO for matching RQST_EMPL_ID. Else null. | |
| RQST_PHONE_ID | From input file (or staging table) if available. Else EMPL_PHONE.PHONE_ID from row with the lowest SEQ_NO for matching RQST_EMPL_ID. Else null. | |
| RVSN_NO | 0 | |
| S_REAPPRVL_CD | N | |
| S_RQ_STATUS_CD | (RQ_SETTINGS. S_APPR_PROC_ASG_CD not N, S_APPRVL_LVL_CD = R for user’s COMPANY_ID),
if Submit for Approval = Submit Only In-Approval, value of RQ_HDR.S_RQ_STATUS_CD will be "P", "I" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. if Submit for Approval = Submit All, value of RQ_HDR.S_RQ_STATUS_CD will be "I", "I" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. if Submit for Approval = Submit None, value of RQ_HDR.S_RQ_STATUS_CD will be "P", "P" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. If approvals are NOT required (RQ_SETTINGS. S_APPR_PROC_ASG_CD = N for user’s COMPANY_ID), if Submit for Approval = Submit Only In-Approval, value of RQ_HDR.S_RQ_STATUS_CD will be "P", "A" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. if Submit for Approval = Submit All, value of RQ_HDR.S_RQ_STATUS_CD will be "A", "A" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. if Submit for Approval = Submit None, value of RQ_HDR.S_RQ_STATUS_CD will be "P", "P" or "V" for Req Hdr input file Approval Status = "P", "I" or "V" respectively. If approvals are required at line level (RQ_SETTINGS. S_APPRVL_LVL_CD = L, S_APPR_PROC_ASG_CD not N for user’s COMPANY_ID) and at least one line is "I", make this "I". If approvals are required at line level (RQ_SETTINGS. S_APPRVL_LVL_CD = L, S_APPR_PROC_ASG_CD not N for user’s COMPANY_ID) and at least one line is "P," make this "P." |
|
| TGT_PLACE_DT | From input file (or staging table) if available. Else null. | |
| TIME_STAMP | System Date & Time | |
| TOTAL_EST_REQ_AMT | Sum of requisition line total amounts (RQ_LN. EST_RQ_LN_TOT_AMT) | |
| TRN_CRNCY_CD | From input file (or staging table)
Else, Functional Currency Code (GL_CONFIG.FUNC_S_CRNCY_CD). |
|
| TRN_CRNCY_DT | From input file (or staging table)
Else, current date. |
|
| TRN_FREEZE_RT_FL | Set to N if RQ_HDR. S_RQ_STATUS_CD = P(ending)
Else set to Y. |
|
| TRN_SALES_TAX_AMT | Sum of requisition line sales tax total amounts (RQ_LN. TRN_SALES_TAX_AMT) | |
| TRN_TO_EUR_RT | Default value [1/MU_CRNCY_STATUS. EURO_TO_CRNCY_RT)] for matching transaction currency code (TRN_CRNCY_CD).
Else if unavailable, set to 1 |
|
| TRN_TO_EUR_RT_FL | Set to Y if the transaction currency is a Euro currency (row exists in MU_CRNCY_STATUS table with matching TRN_CRNCY_CD).
Else set to N. |
|
| TRN_TOTAL_REQ_AMT | Sum of requisition line total amounts (RQ_LN. TRN_RQ_LN_TOT_AMT) | |
| SUBC_AGR_FL | Input file | |
| US_CITIZEN_FL | Input file (SUBC_AGR_FL = Y). Else Null. | |
| ITAR_AUTH_FL | Input file (SUBC_AGR_FL = Y). Else Null. | |
| SEC_CLR_CD | Input file (SUBC_AGR_FL = Y). Else Null. | |
| AGENCY_NAME | Input file (SUBC_AGR_FL = Y). Else Null. |
Table Name: RQ_HDR_TEXT
| Column | Value/Source |
|---|---|
| MODIFIED_BY | User ID |
| ROWVERSION | 0 |
| RQ_ID | From input file (or staging table) |
| SEQ_NO | From input file (or staging table).
Ver 6.0 only – CP2006 MM006: For automatically loaded rows as per logic below, default in the original sequence number from the standard text row (even if it is a duplicate of existing sequence numbers). |
| TEXT_CD | From input file (or staging table).
Ver 6.0 only – CP2006 MM006: Also automatically default in all Standard Text Codes with Where-Used = Purchase Requisition and Automatically Default = Y (TEXT_WHERE_USED. S_WHERE_USED_CD = R and AUTODFLT_FL = Y). Exclude standard text rows that have already been added via the input file (or staging table). These additional rows should load after any rows from input file and with their original default sequence numbers. |
| TIME_STAMP | System Date & Time |
Table Name: RQ_HDR_APPRVL
Populated if RQ_SETTINGS.S_APPRVL_LVL_CD = R & RQ_HDR.S_RQ_STATUS_CD = I.
Check the Req Settings S_APPRVL_LVL_CD to determine if approval processes are to be assigned at the header or line level. If approvals are done at the header level, always leave the RQ_LN approval process code null. If approvals are done at the line level, always leave the RQ_HDR approval process code null. See write-up.
| Column | Value/Source |
|---|---|
| APPRVL_DTT | Null |
| APPRVL_EMPL_ID | Null |
| APPRVL_NOTES | Null |
| APPRVL_SEQ_NO | APPRVL_PROC_TITLE. APPRVL_SEQ_NO |
| APPRVL_USER_ID | Null |
| CHK_ORG_FL | APPRVL_PROC_TITLE. CHK_ORG_FL |
| CHK_PROJ_FL | APPRVL_PROC_TITLE. CHK_PROJ_FL |
| COMPANY_ID | |
| MODIFIED_BY | User ID |
| NEXT_SEQ_FL | All rows with an approval type of electronic or both electronic & signature (RQ_HDR_APPRVL. S_RQ_APPR_TYPE = E or B), and with the lowest approval sequence number APPRVL_SEQ_NO (it is possible to have more that one approval title with the same sequence number) will have their NEXT_SEQ_FL set to "Y." Others will have it set to "N." |
| PAO_APPRVL_FL | APPRVL_PROC_TITLE. PAO_APPRVL_FL |
| QC_APPRVL_FL | APPRVL_PROC_TITLE. QC_APPRVL_FL |
| ROWVERSION | 0 |
| RQ_APPR_TITLE_DC | APPRVL_PROC_TITLE. RQ_APPR_TITLE_DC |
| RQ_ID | From RQ_HDR.RQ_ID |
| RVSN_NO | 0 |
| RSN_CD | Null |
| S_RQ_APPR_TYPE | APPRVL_PROC_TITLE. S_RQ_APPR_TYPE |
| S_RQ_APPRVL_CD | P |
| S_RSN_WH_USED_CD | Null |
| TIME_STAMP | System Date & Time |
Table Name: RQ_LN
| Column | Value/Source |
|---|---|
| APPROVAL_DT | If saved as approved, load system date.
Null |
| BILL_CYCLE_CD | Null |
| BILL_CYCLE_PAY_AMT | 0 |
| BOM_CONFIG_CD | From input file (or staging table), else null |
| BUYER_ASSIGN_DT | From line input file (or staging table) if requisition settings has manual buyer assignment (RQ_SETTINGS. S_BUYER_ASSIGN_CD = M.
Else, if RQ_LN.BUYER_ID is not null, load system date. Else, if header input file (or staging table) RQ_HDR.BUYER_ID is loaded (as per BUYER_ID logic below), load from header input file RQ_HDR.BUYER_ASSIGN_DT (if available). Else, if header input file (or staging table) does not have RQ_HDR.BUYER_ASSIGN_DT, load system date. Else, null. |
| BUYER_ID | From input file (or staging table) if requisition settings has manual buyer assignment (RQ_SETTINGS. S_BUYER_ASSIGN_CD = M.
Also from input file (or staging table) for other types of buyer assignment (RQ_SETTINGS.S_BUYER_ASSIGN_CD = C, V, I, OR P) If RQ_SETTINGS.BUYER_OVRIDE_FL = Y. Else, from header input file (or staging table) if requisition settings has manual buyer assignment (RQ_SETTINGS. S_BUYER_ASSIGN_CD = M. Also from header input file (or staging table) for other types of buyer assignment (RQ_SETTINGS.S_BUYER_ASSIGN_CD = C, V, I, OR P) If RQ_SETTINGS.BUYER_OVRIDE_FL = Y. Else, load as follows depending upon the value of RQ_SETTINGS. S_BUYER_ASSIGN_CD. If "P" (Proj/Acct/Org), default BUYER_PROJ. BUYER_ID or BUYER_ORG_ACCT. BUYER_ID from row with DEFAULT_FL = Y corresponding to the project or account/org of requisition line. If "V" (Preferred Vendor), default BUYER_VEND. BUYER_ID corresponding to the Preferred Vendor on the requisition line. If "I" (Item/Misc Type), default ITEM.BUYER_ID corresponding to the requisition line item. If "C" (Commodity), default COMM. BUYER_ID corresponding to the requisition line item commodity code (ITEM. COMM_CD). Else, null. |
| CERT_OF_CNFRM_FL | From input file (or staging table) only if RQ_SETTINGS.COFC_OVRIDE_FL = Y for user’s COMPANY_ID.
If flag = N, load from PART_PROJ. CERT_OF_CONFRM_FL for associated project. If PART_PROJ row does not exist, load from PART.CERT_OF_CONFRM_FL. Else, N. |
| CLIN_ID | From input file (or staging table), else null |
| COMM_CD | From input file (or staging table)
Else, if Item is entered, load from ITEM. Else, Null |
| COMPL_DT | Null |
| DEL_TO_FLD | From input file (or staging table)
Else, Space |
| DROP_SHIP_FL | From input file (or staging table)
Else, N |
| EST_LN_CHG_TAX_AMT | Sum of all line charge rows for the requisition line (SUM of RQ_LN_CHG. SALES_TAX_AMT rows for matching RQ_ID/RQ_LN_KEY). |
| EST_NET_UNIT_AMT | Converted from TRN_NET_UNIT_AMT |
| EST_RQ_LN_CHG_AMT | Sum of all line charge rows for the requisition line (SUM of RQ_LN_CHG. CHG_CST_AMT rows for matching RQ_ID/RQ_LN_KEY). |
| EST_RQ_LN_EXT_AMT | If Quantity > 0, Value = RQSTD_QTY * EST_NET_UNIT_AMT.
Else, convert from TRN_RQ_LN_EXT_AMT. |
| EST_RQ_LN_TOT_AMT | EST_RQ_LN_EXT_AMT + EST_RQ_LN_CHG_AMT + EST_SALES_TAX_AMT |
| EST_SALES_TAX_AMT | Sum of (a) requisition line sales tax amount + (b) total of sales tax amount of all taxable line charge rows for that requisition line.
If RQ_LN.TAXABLE_FL=Y, calculate requisition line sales tax = (RQ_LN. EST_RQ_LN_EXT_AMT * RQ_LN. SALES_TAX_RT). If RQ_LN_CHG. TAXABLE_FL=Y, for each taxable requisition line charge, calculate sales tax = (RQ_LN_CHG. CHG_CST_AMT * RQ_LN.SALES_TAX_RT). Add them to get total line charge sales tax amount. Use Sales Tax Rate associated with the requisition line Ship ID (SALES_TAX_CD. SALES_TAX_RT for SHIP_ID.SALES_TAX_CD corresponding to RQ_LN.SHIP_ID). |
| GENERATED_AMT | 0 |
| GENERATED_QTY | 0 |
| IND_CLASS_CD | From input file (or staging table)
Else, if requisition line type is M, load LN_CHG_TYPE.IND_CLASS_CD. Else, if requisition line type is not M, load ITEM.IND_CLASS_CD. Else, Null. |
| INVT_ABBRV_CD | From input file (or staging table)
Else, Null |
| ITEM_ID | From input file (or staging table)
Else, space |
| ITEM_KEY | |
| ITEM_RVSN_ID | From input file (or staging table)
Else, null |
| MANUF_ID | From input file (or staging table)
Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART.MANUF_ID from row with matching VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART.MANUF_ID from row with ALT_PART.PREF_FL = Y. If the ITEMS_BY_COMP_FL = N, DB selects on ALT_PART should only allow rows with a company ID of null or equal to the user’s company ID. Else, null. |
| MANUF_PART_ID | From input file (or staging table)
Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. MANUF_PART_ID from row with matching VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. MANUF_PART_ID from row with ALT_PART.PREF_FL = Y. If the ITEMS_BY_COMP_FL = N, DB selects on ALT_PART should only allow rows with a company ID of null or equal to the user’s company ID. Else, space. |
| MANUF_PART_RVSN_ID | From input file (or staging table)
Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. MANUF_PART_RVSN_ID from row with matching VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. MANUF_PART_RVSN_ID from row with ALT_PART.PREF_FL = Y. If the ITEMS_BY_COMP_FL = N, DB selects on ALT_PART should only allow rows with a company ID of null or equal to the user’s company ID. Else, space. |
| MIL_SPEC_ID | From input file (or staging table)
Else, PART.MIL_SPEC_ID if requisition line type is part (ITEM. S_ITEM_TYPE = P). Else, null. |
| MISC_LN_CHG_TYPE | From input file (or staging table)
Else, null |
| MODIFIED_BY | User ID |
| NEXT_APPRVL_SEQ_NO | 0 |
| NSN_ID | From input file (or staging table)
Else, PART.NSN_ID if requisition line type is part (ITEM. S_ITEM_TYPE = P). Else, null. |
| OP_ID | From input file (or staging table)
Else, null |
| ORDER_REF_ID | From input file (or staging table)
Else, space. |
| ORDER_REF_LN_KEY | Same as ORDER_REF_LN_NO |
| ORDER_REF_LN_NO | From input file (or staging table). Else 0. |
| OVRSHP_ALLOW_FL | From input file (or staging table).
Else, if requisition line type =M, load LN_CHG_TYPE.OVRSHP_ALLOW_FL. Else, if requisition line type is not M, load ITEM.OVRSHP_ALLOW_FL. |
| PERF_END_DT | From input file (or staging table)
Else, null |
| PERF_START_DT | From input file (or staging table)
Else, null |
| PLANNER_ID | From input file (or staging table)
Else, if Inv Abbrev is loaded, default value based on PC_SETTINGS. S_PLANNER_ASSIGN_CD. If "N" load NET_GRP.PLANNER_ID corresponding to INVT_PROJ.NET_GRP_ID. If "I" load INVT_PROJ.PLANNER_ID. If "P" load PART.PLANNER_ID associated with the line part. If "C" load COMM.PLANNER_ID associated with the line part. Else, null |
| PREF_QUOTE_ID | From input file (or staging table)
Else, space. |
| PREF_VEND_ID | From input file (or staging table).
Else, if Suggested Blanket PO is entered on the requisition line, load associated PO_HDR. VEND_ID. Else, if quote is assigned, load QT_HDR. VEND_ID. Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in that row’s ITEM_VEND.VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), but a row exists in ALT_PART for that part with ALT_PART. PREF_FL = Y, load in that row’s VEND_ID. Else, null. |
| PROCURE_TYPE_CD | From input file (or staging table)
Else, null |
| QC_REQD_FL | From input file (or staging table) only if RQ_SETTINGS. QC_REQD_OVRIDE_FL = Y for user’s COMPANY_ID.
If flag = N, load from PART_PROJ. QC_REQD_FL for associated project. If PART_PROJ row does not exist load from PART.QC_REQD_FL. Else, if the Miscellaneous Type is not null, load LN_CHG_TYPE. QC_REQD_FL Else, N |
| RECPT_TOL_PCT_RT | From input file (or staging table). Note – To be consistent with Enter Requisition application, round to 2 decimals even though database allows up to 4 decimals.
Else, if ITEM_SETTINGS. RECPT_TOL_FL = N, or if ITEM.OVRSHP_ALLOW_FL = N, default 0. Else, if requisition line type is not M, load ITEM.RECPT_TOL_PCT_RT. Else, if requisition line type is M, default to 0. |
| ROWVERSION | 0 |
| RQ_APPR_PROC_CD | If approvals are required at requisition line level (RQ_SETTINGS. S_APPR_PROC_ASG_CD not N, S_APPRVL_LVL_CD = L), default as follows depending upon the value of RQ_SETTINGS. S_APPR_PROC_ASG_CD.
If "G" (Global), default RQ_SETTINGS. RQ_APPR_PROC_CD. If "M" (Manual), load from input file (if available); else default INVT_PROJ. RQ_APPR_PROC_CD for associated line (if not null); else from RQ_SETTINGS. RQ_APPR_PROC_CD. If "P" (Proj/Acct/Org), default from PROJ. RQ_APPR_PROC_CD or ORG_ACCT. RQ_APPR_PROC_CD for associated line. If "I" (Item), default RQ_APPRVL_PROC. RQ_APPR_PROC_CD from row associated with the item type of the requisition line (for example select value from row with PART_TYPE_FL = Y if req line is a Part, etc). Else null |
| RQ_DT | From RQ_HDR.RQ_DT |
| RQ_EST_CST_TYPE_CD | From input file (or staging table).
Else, load RQ_EST_CST_TYPE. RQ_EST_CST_TYP_CD from row with the lowest non-zero PREF_SEQ_NO having an associated non-zero cost in ITEM_CST (for cost types IL, IS, IR) or ITEM_PROJ_CST (for cost types PL, PS, PR.). If it equals zero, read the costs value for the type with the next lowest sequence number. Else, null. |
| RQ_ID | From input file (or staging table). |
| RQ_LN_DESC | If new line and no description is provided, load from LN_CHG_DESC if Misc Line Charge Type is entered and ITEM_DESC if Item is entered.
For new req line and Item is entered – If the PD Settings Allow Description Change flag = N, ignore the description (if any) in the input file. If the PD Settings Allow Description Change flag = Y, use input file description if provided. For existing req line, if Item is changed - Default in the description from Item. If the PD Settings Allow Description Change flag = N, ignore the description (if any) in the input file. If the PD Settings Allow Description Change flag = Y, use input file description if provided; else use the item default. For existing req line, if Item is not changed - If the Line Type is "G" (Good) or "S" (Service), and Allow Description Change in Purchasing - Goods and Services is not checked in Product Definition Settings, always load from ITEM_DESC. If Line Type is P(art), and Allow Description Change in Purchasing - Part is not checked in Product Definition Settings, always load from ITEM_DESC. If the PD Settings Allow Description Change flag = Y in both cases, load the description from the input file (if provided); else use existing req line description. |
| RQ_LN_KEY | System assigned, same as RQ_LN_NO. |
| RQ_LN_NO | From input file (or staging table). |
| RQ_LN_PROC_FL | N |
| RQ_LN_RVSN_NO | 0 |
| RQ_LN_UM_CD | From input file (or staging table).
Else, Order U/M for line item (ITEM_UM. UM_CD where S_UM_TYPE = O) if it exists Else, Default U/M for line item (ITEM.DFLT_UM_CD). If Misc Type Code is entered or changed, load from LN_CHG_TYPE.UM_CD corresponding to the RQ_LN.MISC_LN_CHG_TYPE |
| RQST_DT | From input file (or staging table). |
| RQST_INT_NOTES | From input file (or staging table).
Else, space. |
| RQSTD_QTY | From input file (or staging table).
Else, 0. |
| S_ORD_REF_TYPE_CD | From input file (or staging table).
Else, null. |
| S_PO_LN_TYPE | If item is entered, load ITEM.S_ITEM_TYPE.
Else if misc type is entered load "M" |
| S_REAPPRVL_CD | Null |
| S_RQ_STATUS_CD | If approvals are required at requisition line level (RQ_SETTINGS. S_APPR_PROC_ASG_CD not N, S_APPRVL_LVL_CD = L for user’s COMPANY_ID),
if Submit for Approval = Submit Only In-Approval, value of RQ_LN.S_RQ_STATUS_CD will be "P," "I," or "V" for Req Ln input file Approval Status = "P," "I," or "V" respectively. if Submit for Approval = Submit All, value of RQ_LN.S_RQ_STATUS_CD will be "I," "I" or "V" for Req Ln input file Approval Status = "P," "I" or "V" respectively. if Submit for Approval = Submit None, value of RQ_LN.S_RQ_STATUS_CD will be "P," "P" or "V" for Req Ln input file Approval Status = "P," "I" or "V" respectively. If approvals are NOT required (RQ_SETTINGS. S_APPR_PROC_ASG_CD = N for user’s COMPANY_ID), if Submit for Approval = Submit Only In-Approval, value of RQ_LN.S_RQ_STATUS_CD will be "P," "A" or "V" for Req Ln input file Approval Status = "P," "I" or "V" respectively. if Submit for Approval = Submit All, value of RQ_LN.S_RQ_STATUS_CD will be "A," "A" or "V" for Req Ln input file Approval Status = "P," "I" or "V" respectively. if Submit for Approval = Submit None, value of RQ_LN.S_RQ_STATUS_CD will be "P," "P" or "V" for Req Ln input file Approval Status = "P," "I" or "V" respectively. If approval is at the header level (RQ_SETTINGS. S_APPRVL_LVL_CD = R , S_APPR_PROC_ASG_CD not N for user’s COMPANY_ID), default to RQ_HDR. S_RQ_STATUS_CD, unless input file value is "V" (Void) in which case leave it as "V." |
| SHIP_ID | From input file (or staging table).
Else, null. |
| SHIP_VIA_FLD | From input file (or staging table).
Else, space. |
| SO_LN_COMP_KEY | |
| SRCE_INSP_FL | From input file (or staging table) only if RQ_SETTINGS. SRCE_INSP_OVRD_FL = Y for user’s COMPANY_ID.
If flag = N, load from PART_PROJ. SRCE_INSP_FL for associated project. If PART_PROJ row does not exist, load from PART.SRCE_INSP_FL. Else, N. |
| SUG_BLKT_PO_ID | From input file (or staging table).
Else, if requisition line is an item on an existing Open blanket PO line with a S_PURCH_AGRMT_CD of "S" or "A" (and meets other criteria as per write-up section), load the Blanket PO_ID. Else, null. |
| TAXABLE_FL | From input file (or staging table).
Else, load Y or N depending upon PO_SETTINGS. S_PO_TAXABLE_TYPE. If it is T, load Y. If it is N, load N. If it is I, check ITEM_SETTINGS. Depending upon the requisition line type load Y or N based on Y or N values for PART_TAXABLE_FL, GOOD_TAXABLE_FL, SERV_TAXABLE_FL. For miscellaneous lines check LN_CHG_TYPE. TAXABLE_FL. |
| TGT_PLACE_DT | From input file (or staging table).
Else, from header input file (or staging table). Else, null. |
| TIME_STAMP | System Date & Time |
| TRN_BILL_CYCLE_AMT | 0 |
| TRN_GENERATED_AMT | 0 |
| TRN_LN_CHG_TAX_AMT | Sum of all line charge rows for the requisition line (SUM of RQ_LN_CHG. TRN_LN_CHG_TAX_AMT rows for matching RQ_ID/RQ_LN_KEY). |
| TRN_NET_UNIT_AMT | From input file (or staging table).
Else, if RQ_EST_CST_TYP_CD is loaded on the line (see above) load the associated non-zero cost from ITEM_CST (for cost types IL, IS, IR) or ITEM_PROJ_CST (for cost types PL, PS, PR.). Else, if quote is assigned, load QT_LN_BRK. TRN_NT_UN_CST_AMT. Else, 0. |
| TRN_RQ_LN_CHG_AMT | Sum of all line charge rows for the requisition line (SUM of RQ_LN_CHG. TRN_CHG_CST_AMT rows for matching RQ_ID/RQ_LN_KEY). |
| TRN_RQ_LN_EXT_AMT | If Quantity > 0, Value = RQSTD_QTY * TRN_NET_UNIT_AMT
Else, from input file (or staging table). |
| TRN_RQ_LN_TOT_AMT | TRN_RQ_LN_EXT_AMT + TRN_LN_CHG_AMT + TRN_SALES_TAX_AMT + TRN_LN_CHG_TAX_AMT |
| TRN_SALES_TAX_AMT | Sum of (a) requisition line sales tax amount + (b) total of sales tax amount of all taxable line charge rows for that requisition line.
If RQ_LN.TAXABLE_FL=Y and PO_SETTINGS. CALC_TAX_FL=Y, calculate requisition line sales tax = (RQ_LN. TRN_RQ_LN_EXT_AMT * RQ_LN. SALES_TAX_RT). If RQ_LN_CHG. TAXABLE_FL=Y and PO_SETTINGS. CALC_TAX_FL=Y, for each taxable requisition line charge, calculate sales tax = (RQ_LN_CHG. TRN_CHG_CST_AMT * RQ_LN.SALES_TAX_RT). Add them to get total line charge sales tax amount. Use Sales Tax Rate associated with the requisition line Ship ID (SALES_TAX_CD. SALES_TAX_RT for SHIP_ID.SALES_TAX_CD corresponding to RQ_LN.SHIP_ID). |
| USER_RVSN_FLD | From Input File (or staging tables) if ITEM_SETTINGS_CORP. USE_PART_RVSN_FL = N.
Else, ITEM. ITEM_LAST_RVSN_ID. |
| VEND_PART_ID | From input file (or staging table)
Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. VEND_PART_ID from row with matching VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. VEND_PART_ID from row with ALT_PART.PREF_FL = Y. If the ITEMS_BY_COMP_FL = N, DB selects on ALT_PART should only allow rows with a company ID of null or equal to the user’s company ID. Else, space. |
| VEND_PART_RVSN_ID | From input file (or staging table)
Else, if requisition line item is a part with a preferred vendor associated with it (row exists in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. VEND_PART_RVSN_ID from row with matching VEND_ID. Else, if requisition line item is a part without a preferred vendor associated with it (row does not exist in ITEM_VEND with PREF_VEND_CD = Y), load in ALT_PART. VEND_PART_RVSN_ID from row with ALT_PART.PREF_FL = Y. If the ITEMS_BY_COMP_FL = N, DB selects on ALT_PART should only allow rows with a company ID of null or equal to the user’s company ID. Else, space. |
| VOID_REJECT_DT | If line status is "V" (Void), load system date.
Else Null |
| WHSE_ID | From input file (or staging table).
Else, if inv abbreviation is loaded, default in the associated INVT_PROJ.WHSE_ID. Else, null. |
| US_CITIZEN_FL | Input File (SUBC_AGR_FL = Y). Else Null. |
| ITAR_AUTH_FL | Input File (SUBC_AGR_FL = Y). Else Null. |
| SEC_CLR_CD | Input File (SUBC_AGR_FL = Y). Else Null. |
| AGENCY_NAME | Input File (SUBC_AGR_FL = Y). Else Null. |
Table Name: RQ_LN_ACCT
| Column | Value/Source | Comments |
|---|---|---|
| ACCT_ID | (a) From input file (or staging table).
Else (b) If a valid Inventory Abbreviation code is entered in the input file (or staging table), load INVT_ABBRV_CD. MATL_ACCT_ID. Else (c) If Project Account Abbreviation is entered in the input file (or staging table), the Project/Account Abbreviation and the Account Group of the Project are looked up in the Account Entry Groups table to get the Account. |
|
| CST_AMT | Convert from TRN_CST_AMT | |
| CST_AMT_PCT_RT | From input file (or staging table). Note – To be consistent with Enter Requisition application, round to 2 decimals even though database allows up to 4 decimals. | |
| MODIFIED_BY | User ID | |
| ORG_ABBRV_CD | (a) From input file (or staging table).
Else (b) If Org is entered, ORG. ORG_ABBRV_CD. Else (d) Null. |
|
| ORG_ID | (a) From input file (or staging table).
Else (b) If a valid Inventory Abbreviation code is entered in the input file (or staging table), load INVT_ABBRV_CD. MATL_ORG_ID. Else (c) If the Project is supplied, load PROJ.ORG_ID. |
|
| PROJ_ABBRV_CD | (a) From input file (or staging table).
Else (b) If project is entered, PROJ. PROJ_ABBRV_CD. Else (d) Null. |
|
| PROJ_ACCT_ABBRV_CD | (a) From input file (or staging table).
Else (c) Null. |
|
| PROJ_ID | (a) From input file (or staging table).
Else (b) If a valid Inventory Abbreviation code is entered, load INVT_ABBRV_CD. PROJID. Else (d) Null. |
|
| REF_STRUC_1_ID | (a) From input file (or staging table).
Else (c) Null. |
|
| REF_STRUC_2_ID | (a) From input file (or staging table).
Else (c) Null. |
|
| ROWVERSION | 0 | |
| RQ_ID | From Input file (or staging table) | |
| RQ_LN_KEY | RQ_LN.RQ_LN_KEY | |
| SUB_KEY | System assigned. One for each Account Line.
If the data being processed is a change to an existing line account row, the value from the existing row is used. |
|
| TIME_STAMP | System Date & Time | |
| TRN_CST_AMT | From Input file (or staging table) | Do not save if value = 0. |
Table Name: RQ_LN_APPRVL
Populated if RQ_SETTINGS. S_APPRVL_LVL_CD = L & RQ_HDR.S_RQ_STATUS_CD = I.
Check the Req Settings S_APPRVL_LVL_CD to determine if approval processes are to be assigned at the header or line level. If approvals are done at the header level, always leave the RQ_LN approval process code null. If approvals are done at the line level, always leave the RQ_HDR approval process code null. See write-up.
| Column | Value/Source |
|---|---|
| APPRVL_DTT | Null |
| APPRVL_EMPL_ID | Null |
| APPRVL_NOTES | Null |
| APPRVL_SEQ_NO | APPRVL_PROC_TITLE. APPRVL_SEQ_NO |
| APPRVL_USER_ID | Null |
| CHK_ORG_FL | APPRVL_PROC_TITLE. CHK_ORG_FL |
| CHK_PROJ_FL | APPRVL_PROC_TITLE. CHK_PROJ_FL |
| COMPANY_ID | |
| MODIFIED_BY | User ID |
| NEXT_SEQ_FL | Row with the lowest value of associated RQ_LN_APPRVL. APPRVL_SEQ_NO will have this flag equal to "Y" (could be multiple rows). Others will have it set to "N." |
| PAO_APPRVL_FL | APPRVL_PROC_TITLE. PAO_APPRVL_FL |
| PAO_APPRVL_FL | APPRVL_PROC_TITLE. PAO_APPRVL_FL |
| QC_APPRVL_FL | APPRVL_PROC_TITLE. QC_APPRVL_FL |
| ROWVERSION | 0 |
| RQ_APPR_TITLE_DC | APPRVL_PROC_TITLE. RQ_APPR_TITLE_DC |
| RQ_ID | From input file (or staging table). |
| RQ_LN_KEY | RQ_LN.RQ_LN_KEY |
| RQ_LN_RVSN_NO | 0 |
| RSN_CD | Null |
| S_RQ_APPR_TYPE | APPRVL_PROC_TITLE. S_RQ_APPR_TYPE |
| S_RQ_APPRVL_CD | P |
| S_RSN_WH_USED_CD | Null |
| TIME_STAMP | System Date & Time |
Table Name: RQ_LN_TEXT
| Column | Value/Source |
|---|---|
| MODIFIED_BY | User ID |
| ROWVERSION | 0 |
| RQ_ID | From input file (or staging table). |
| RQ_LN_KEY | RQ_LN.RQ_LN_KEY |
| TEXT_CD | From input file (or staging table). In addition,
For each req line, search the ITEM_TEXT, PROJ_TEXT and ITEM_PROJ_TEXT for that part, project and part/project respectively. Only add those text codes with a S_WHERE_USED_CD = "P" and/or "R" on the TEXT_WHERE_USED table. Do not include duplicate text codes. |
| S_TEXT_SRCE_CD | Fill in the S_TEXT_SRCE_CD based on the table the text code was pulled from: "I" for Item text, "P" for Project text, and "R" for ITEM_PROJ_TEXT. |
| SEQ_NO | From input file (or staging table) |
| TIME_STAMP | System Date & Time |
Table Name: RQ_LN_CHG
| Column | Value/Source |
|---|---|
| CHG_CST_AMT | Converted from TRN_CHG_CST_AMT |
| CHG_DESC | From input file (or staging table).
Else LN_CHG_TYPE.LN_CHG_DESC |
| LN_CHG_TYPE | From input file (or staging table). |
| MODIFIED_BY | User ID |
| ROWVERSION | 0 |
| RQ_ID | From input file (or staging table). |
| RQ_LN_KEY | RQ_LN.RQ_LN_KEY |
| SALES_TAX_AMT | If RQ_LN_CHG. TAXABLE_FL=Y, calculate (RQ_LN_CHG.CHG_CST_AMT * RQ_LN.SALES_TAX_RT).
Use Sales Tax Rate associated with the requisition line Ship ID (SALES_TAX_CD. SALES_TAX_RT for SHIP_ID.SALES_TAX_CD corresponding to RQ_LN.SHIP_ID). Else 0. |
| SUB_KEY | System assigned. One for each Charge Line. |
| TAXABLE_FL | From input file (or staging table).
Else LN_CHG_TYPE.TAXABLE_FL |
| TIME_STAMP | System Date & Time |
| TRN_CHG_CST_AMT | From input file (or staging table) |
| TRN_SALES_TAX_AMT | If RQ_LN_CHG. TAXABLE_FL=Y, calculate (RQ_LN_CHG.TRN_CHG_CST_AMT * RQ_LN.SALES_TAX_RT).
Use Sales Tax Rate associated with the requisition line Ship ID (SALES_TAX_CD. SALES_TAX_RT for SHIP_ID.SALES_TAX_CD corresponding to RQ_LN.SHIP_ID). Else 0. |
Table Name: RQ_LN_NOTES
| Column | Value/Source |
|---|---|
| MODIFIED_BY | User ID |
| ROWVERSION | 0 |
| RQ_ID | From input file (or staging table) |
| RQ_LN_KEY | RQ_LN.RQ_LN_KEY |
| RQ_LN_TX | From input file (or staging table). |
| TIME_STAMP | System Date & Time |
Table Name: RQ_HDR_SCISAP
This applies only if RQ_HDR.SUBC_AGR_FL=Y. Else, ignore input file value (do not insert row).
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| SCISAP_CLR_CD | Input file |
| ROWVERSION | |
| MODIFIED BY | |
| TIME_STAMP |
Table Name: RQ_HDR_SOW
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| SOW_KEY | |
| SOW_ID | Input file |
| SOW_RVSN_ID | Input file |
| ROWVERSION | |
| MODIFIED_BY | |
| TIME_STAMP |
Table Name: RQ_LN_SCISAP
This applies only if RQ_HDR.SUBC_AGR_FL = Y. Else, ignore input file value (do not insert row).
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| RQ_LN_KEY | |
| SCISAP_CLR_CD | Input file |
| ROWVERSION | |
| MODIFIED_BY | |
| TIME_STAMP |
Table Name: RQ_LN_RESOURCE
This applies only if RQ_HDR.SUBC_AGR_FL = Y. Else, ignore input file value (do not insert row).
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| RQ_LN_KEY | |
| RESOURCE_LN_KEY | Input file |
| RSRCE_LN_NO | |
| ITEM_KEY | Input file |
| ITEM_ID | Input file |
| ITEM_RVSN_ID | Input file |
| RESOURCE_DESC | Input file or derived |
| NO_OF_RESOURCES | Input file |
| HRS | Input file |
| LAB_RT_AMT | Derived |
| TRN_LAB_RT_AMT | Input file |
| BILL_LAB_CAT_Cd | Input file |
| GENL_LAB_CAT_Cd | Input file |
| CITY_NAME | Input file |
| MAIL_STATE_DC | Input file |
| POSTAL_CD | Input file |
| COUNTRY_CD | Input file |
| ROWVERSION | |
| MODIFIED_BY | |
| TIME_STAMP |
Table Name: RQ_LN_RSRCE_VEND
This applies only if RQ_HDR.SUBC_AGR_FL = Y. Else, ignore input file value (do not insert row).
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| RQ_LN_KEY | |
| RSRCE_LN_KEY | Input file |
| VEND_EMPL_ID | Input file |
| COMPANY_ID | Login User company |
| ROWVERSION | |
| MODIFIED_BY | |
| TIME_STAMP |
Table Name: RQ_LN_SOW
| Column | Value/Source |
|---|---|
| RQ_ID | Input file |
| RQ_LN_KEY | |
| SOW_KEY | |
| SOW_ID | Input file |
| SOW_RVSN_ID | Input file |
| ROWVERSION | |
| MODIFIED_BY | |
| TIME_STAMP |