AR_CAGL_VW

(SQL View)
 Index Back

Contracts to GL view

Used to determine if contract has been sucessfully budget checked.

SELECT A.BUSINESS_UNIT , A.CUST_ID , A.ITEM , A.ITEM_LINE FROM PS_ITEM_ACTIVITY A , PS_BI_HDR H , PS_BI_LINE B , PS_CA_DETAIL C , PS_CA_ACCTG_LN_PC P WHERE A.ENTRY_USE_ID IN ('IT-01', 'IT-02') AND A.ORIGIN_ID = ( SELECT B.ORIGIN_ID_BILLING FROM PS_BUS_UNIT_OPT_BI B , PS_SET_CNTRL_REC S WHERE B.SETID = S.SETID AND S.SETCNTRLVALUE = A.BUSINESS_UNIT_BI AND S.RECNAME = 'BUS_UNIT_OPT_BI') AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.CUST_ID = H.BILL_TO_CUST_ID AND A.ITEM = H.INVOICE AND (A.ITEM_LINE = B.INVOICE_LINE OR (A.ITEM_LINE = 0 AND H.AR_LVL = 'H')) AND H.BUSINESS_UNIT = B.BUSINESS_UNIT AND H.INVOICE = B.INVOICE AND H.GL_LVL = 'B' AND P.CONTRACT_NUM = B.CONTRACT_NUM AND P.CONTRACT_LINE_NUM = B.CONTRACT_LINE_NUM AND P.BUSINESS_UNIT_PC = B.BUSINESS_UNIT AND P.RESOURCE_ID = B.RESOURCE_ID AND P.CONTRACT_NUM = C.CONTRACT_NUM AND P.CONTRACT_LINE_NUM = C.CONTRACT_LINE_NUM AND C.REV_RECOG_METHOD <> '5' AND EXISTS ( SELECT 'X' FROM PS_JRNL_HEADER J WHERE J.BUSINESS_UNIT = P.BUSINESS_UNIT_GL AND J.JOURNAL_ID = P.JOURNAL_ID AND J.JOURNAL_DATE = P.JOURNAL_DATE AND J.BUDGET_HDR_STATUS = 'V')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_FS

2 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

Prompt Table: CUST_BI_A2_VW

3 ITEM Character(30) VARCHAR2(30) NOT NULL Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
4 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line