Contract OLT Detail - by Cont

ct_ca_olt_detail_by_cont

Purpose of Query

Contract OLT Detail - By Contract – This query shows contract lines that are over limit. This query is primarily a central user query but can be run by any agency that uses the Customer Contracts module.

Folder

CO

Tables

A CTW_PROJ_RESRCE - Project Resource

Join B CTW_CA_CNTR_HDR - Customer Contract Header

Note: A JOIN is an SQL command that is used to relate two or more data tables (Records in PeopleSoft) based on the use of related (key) fields from one table to the next. Once joined, data can be retrieved without repeating all of the data in every table.

Prompts

BUSINESS_UNIT - Business Unit – (Equal to)

CONTRACT_NUM - Contract Number – (Equal to)

Criteria

A.ANALYSIS_TYPE - Resource Analysis Type equal to OLT – (Over Limit)

Note: Runtime prompts are included as criteria.

Fields

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text /
1 / A.BUSINESS_UNIT - Business Unit / Char5 / 1 / Contract BU
2 / B.CONTRACT_TYPE - Contract Type / Char15 / 2 / Contract Type
3 / A.CUST_ID - Customer ID / Char15 / 3 / Customer ID
4 / A.CONTRACT_NUM - Contract Number / Char25 / 4 / Contract Number
5 / A.CONTRACT_LINE_NUM - Contract Line Num / Num3.0 / 5 / Contract Line
6 / A.PROJECT_ID - Project / Char15 / 6 / Project
7 / A.ACTIVITY_ID - Activity ID / Char15 / 7 / Activity
8 / A.CT_TXN_LMT_TRNS_ID - Transaction Limit Identifier / Char10 / 8 / Transaction Limit Identifier
9 / A.RESOURCE_ID - Project Transaction ID / Char40 / RESOURCE_ID
10 / A.RESOURCE_ID_FROM - Original Resource ID / Char40 / ResID From
11 / A.BUSINESS_UNIT_GL - General Ledger Unit / Char5 / GL Unit
12 / A.JOURNAL_ID - Journal ID / Char10 / Journal ID
13 / A.JOURNAL_DATE - Journal Date / Date / Date
14 / A.UNPOST_SEQ - UnPost Sequence / Num2.0 / Seq
15 / A.JOURNAL_LINE - GL Journal Line Number / Num9.0 / Line
16 / A.FISCAL_YEAR - Fiscal Year / Num4.0 / Year
17 / A.ACCOUNTING_PERIOD - Accounting Period / Num3.0 / Period
18 / A.ACCOUNT - Account / Char10 / Acct
19 / A.DEPTID - Department / Char10 / DeptID
20 / A.FUND_CODE - Fund Code / Char5 / Fund
21 / A.CLASS_FLD - Class Field / Char5 / Class
22 / A.PROGRAM_CODE - Program Code / Char5 / Program
23 / A.BUDGET_REF - Budget Reference / Char8 / Bud Ref
24 / A.CHARTFIELD1 - Chartfield 1 / Char10 / Chartfield 1
25 / A.CHARTFIELD2 - Chartfield 2 / Char10 / ChartField 2
26 / A.RESOURCE_TYPE - Resource Type / Char5 / Res Type
27 / A.RESOURCE_CATEGORY - Resource Category / Char5 / Category
28 / A.RESOURCE_SUB_CAT - Resource SubCategory / Char5 / SubCat
29 / A.LEDGER_GROUP - Ledger Group / Char10 / Ledger Grp
30 / A.ANALYSIS_TYPE - Resource Analysis Type / Char3 / An Type
31 / A.RES_USER1 - Resource User 1 / Char10 / Res User1
32 / A.RES_USER2 - Resource User 2 / Char10 / Res User2
33 / A.RES_USER3 - Resource User 3 / Char10 / Res User3
34 / A.RES_USER4 - Resource User 4 / Char10 / Res User4
35 / A.RES_USER5 - Resource User 5 / Char10 / Res User5
36 / A.TRANS_DT - Transaction Date / Date / Trans Date
37 / A.ACCOUNTING_DT - Accounting Date / Date / Acctg Date
38 / A.OPRID - User ID / Char30 / User
39 / A.DTTM_STAMP - Date/Time Stamp / DateTm / Date/Time
40 / A.JRNL_LN_REF - Journal Line Reference / Char10 / Ref
41 / A.LINE_DESCR - Journal Line Description / Char30 / Line Descr
42 / A.JRNL_LINE_STATUS - Journal Line Status / Char1 / N / Status
43 / A.JOURNAL_LINE_DATE - Journal Line Date / Date / Line Date
44 / A.FOREIGN_AMOUNT - Foreign Amount / SNm25.3 / Amount
45 / A.PROCESS_INSTANCE - Process Instance / Num10.0 / Instance
46 / A.PC_DISTRIB_STATUS - PC Distribution Status / Char1 / N / PC Status
47 / A.GL_DISTRIB_STATUS - Distribution Status / Char1 / N / Status
48 / A.RESOURCE_STATUS - Resource Status / Char1 / N / Status
49 / A.DESCR - Description / Char30 / Descr
50 / A.SYSTEM_SOURCE - System Source / Char3 / N / Sys Source
51 / A.UNIT_OF_MEASURE - Unit of Measure / Char3 / UOM
52 / A.EMPLID - EmplID / Char11 / ID
53 / A.EMPL_RCD - Empl Rcd Nbr / Num3.0 / Empl Rcd#
54 / A.SEQ_NBR - Sequence Number / Num15.0 / Number
55 / A.TIME_RPTG_CD - Time Reporting Code / Char5 / Time Rptg Cd
56 / A.BUSINESS_UNIT_AP - AP Business Unit / Char5 / AP Unit
57 / A.VENDOR_ID - Vendor ID / Char10 / Vendor
58 / A.VOUCHER_ID - Voucher ID / Char8 / Voucher
59 / A.VOUCHER_LINE_NUM - Voucher Line Number / Num5.0 / Line
60 / A.APPL_JRNL_ID - Journal Template / Char10 / Template
61 / A.DST_ACCT_TYPE - Accounting Entry Type / Char4 / N / Type
62 / A.PO_DISTRIB_STATUS - PO Distribution Status / Char1 / N / PO Distrib
63 / A.BUSINESS_UNIT_PO - PO Business Unit / Char5 / PO Unit
64 / A.REQ_ID - Requisition ID / Char10 / Req ID
65 / A.REQ_LINE_NBR - Requisition Line Number / Num5.0 / Req Line
66 / A.REQ_SCHED_NBR - Requisition Schedule Number / Num3.0 / Req Sched
67 / A.REQ_DISTRIB_NBR - Requisition Distrib Number / Num5.0 / Req Distr
68 / A.PO_ID - Purchase Order / Char10 / PO
69 / A.DUE_DATE - Delivery Due Date from PO / Date / DelivDt-PO
70 / A.LINE_NBR - Line Number / Num5.0 / Line
71 / A.SCHED_NBR - Schedule Number / Num3.0 / Sched Num
72 / A.DISTRIB_LINE_NUM - Distribution Line Number / Num5.0 / Line
73 / A.AM_DISTRIB_STATUS - AM Distribution Status / Char1 / N / AM Dist
74 / A.BUSINESS_UNIT_AM - Asset Management Business Unit / Char5 / AM Unit
75 / A.ASSET_ID - Asset Identification / Char12 / Asset ID
76 / A.PROFILE_ID - Asset Profile ID / Char10 / Profile ID
77 / A.INCENTIVE_ID - Investment Incentive ID / Char10 / Incentive ID
78 / A.CONTRACT_PPD_SEQ - Prepaid Add Sequence / Num5.0 / PPDAddSeq
79 / A.BI_DISTRIB_STATUS - Billing Distribution Status / Char1 / N / BI Distrib
80 / A.BUSINESS_UNIT_BI - Billing Business Unit / Char5 / BI Unit
81 / A.BILLING_DATE - Billing Date / Date / Bill Dt
82 / A.INVOICE - Invoice / Char22 / Invoice
83 / A.REV_DISTRIB_STATUS - Revenue Distribution Status / Char1 / N / Rev Distrib
84 / A.BUSINESS_UNIT_AR - AR Business Unit / Char5 / AR Unit
85 / A.ITEM - Item ID / Char30 / Item ID
86 / A.ITEM_LINE - Item Line / Num6.0 / Line
87 / A.ITEM_SEQ_NUM - Item Sequence Number / Num3.0 / Sequence
88 / A.DST_SEQ_NUM - Distribution Sequence / Num3.0 / Line
89 / A.BUSINESS_UNIT_IN - Inventory Business Unit / Char5 / IN Unit
90 / A.INV_ITEM_ID - Item ID / Char18 / Item
91 / A.EX_DOC_ID - Expense Doc ID / Char10 / Exp Doc ID
92 / A.EX_DOC_TYPE - Expense Doc Type / Char1 / N / Exp Doc Type
93 / A.RESOURCE_QUANTITY - Resource Quantity / SNm14.2 / Quantity
94 / A.RESOURCE_AMOUNT - Resource Amount / SNm25.3 / Amount
95 / A.BUDGET_HDR_STATUS - Budget Checking Header Status / Char1 / N / Budget Status
96 / A.KK_AMOUNT_TYPE - Commitment Control Amount Type / Char1 / N / Amount Type
97 / A.KK_TRAN_OVER_FLAG - Override Transaction / Char1 / N / Override
98 / A.KK_TRAN_OVER_OPRID - Override User ID / Char30 / User
99 / A.KK_TRAN_OVER_DTTM - Override Date / DateTm / DateTime
100 / A.BUDGET_OVER_ALLOW - Budget Overrun Allowed / Char1 / Budget Overrun
101 / A.BUDGET_LINE_STATUS - Budget Checking Line Status / Char1 / N / Budget Status
102 / A.BUDGET_DT - Budget Date / Date / Budg Dt
103 / A.LEDGER - Ledger / Char10 / Ledger
104 / A.BD_DISTRIB_STATUS - BD Distribution Status / Char1 / N / BD Distr Status
105 / A.BUSINESS_UNIT_BD - Budgeting Business Unit / Char5 / BD Unit
106 / A.FA_STATUS - F&A Status / Char1 / F&A Status
107 / A.DT_TIMESTAMP - Date Timestamp / DateTm / Dt Tmstamp
108 / A.VCHR_DIST_LINE_NUM - Distribution Line Number / Num5.0 / Line
109 / A.PM_REVIEWED - Reviewed / Char1 / Reviewed
110 / A.PRICED_RATE - Priced Rate / SNm25.3 / Priced Rate
111 / A.CT_ACTIVITY_ID_DTL - Detail Activity / Char15 / Detail Activity
112 / A.CT_CST_DSTRB_STAT - Cost Distribution Status / Char1 / N / Cost Dist Stat