CM_AP_LIABIL_VW

(SQL View)
 Index Back

CM AP Liability Alert


SELECT a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID , a.BUSINESS_UNIT_IN , a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD ,%Abs(SUM(b.MONETARY_AMOUNT)) ,%Abs(SUM(a.MONETARY_AMOUNT)) , (%Abs(SUM(b.MONETARY_AMOUNT)) - %Abs(SUM(a.MONETARY_AMOUNT))) FROM PS_CM_APRECN_TBL a , PS_CM_APRECN_TBL2 b WHERE a.process_instance = b.process_instance AND a.oprid = b.oprid AND a.RUN_CNTL_ID = b.RUN_CNTL_ID AND a.REQUEST_ID = b.REQUEST_ID AND a.BUSINESS_UNIT_RECV = b.BUSINESS_UNIT_RECV AND a.RECEIVER_ID = b.RECEIVER_ID AND a.RECV_LN_NBR = b.RECV_LN_NBR AND a.RECV_SHIP_SEQ_NBR = b.RECV_SHIP_SEQ_NBR AND A.CM_AP_ERROR_OK = 'N' GROUP BY a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID , a.BUSINESS_UNIT_IN , a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD UNION ALL SELECT a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID , a.BUSINESS_UNIT_IN , a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD , 0 , %Abs(SUM(a.MONETARY_AMOUNT)) , 0 - %Abs(SUM(a.MONETARY_AMOUNT)) FROM PS_CM_APRECN_TBL a WHERE A.CM_AP_ERROR_OK = 'N' AND NOT EXISTS ( SELECT 'x' FROM PS_CM_APRECN_TBL2 b WHERE a.process_instance = b.process_instance AND a.oprid = b.oprid AND a.RUN_CNTL_ID = b.RUN_CNTL_ID AND a.REQUEST_ID = b.REQUEST_ID AND a.BUSINESS_UNIT_RECV = b.BUSINESS_UNIT_RECV AND a.RECEIVER_ID = b.RECEIVER_ID AND a.RECV_LN_NBR = b.RECV_LN_NBR AND a.RECV_SHIP_SEQ_NBR = b.RECV_SHIP_SEQ_NBR) GROUP BY a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID , a.BUSINESS_UNIT_IN , a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD UNION ALL SELECT a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID , ' ' , a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD , %Abs(SUM( a.MONETARY_AMOUNT)) , 0 , %Abs(SUM( a.MONETARY_AMOUNT)) FROM PS_CM_APRECN_TBL2 a WHERE A.CM_AP_ERROR_OK = 'N' AND NOT EXISTS ( SELECT 'x' FROM PS_CM_APRECN_TBL b WHERE a.process_instance = b.process_instance AND a.oprid = b.oprid AND a.RUN_CNTL_ID = b.RUN_CNTL_ID AND a.REQUEST_ID = b.REQUEST_ID AND a.BUSINESS_UNIT_RECV = b.BUSINESS_UNIT_RECV AND a.RECEIVER_ID = b.RECEIVER_ID AND a.RECV_LN_NBR = b.RECV_LN_NBR AND a.RECV_SHIP_SEQ_NBR = b.RECV_SHIP_SEQ_NBR) GROUP BY a.PROCESS_INSTANCE , a.OPRID , a.RUN_CNTL_ID , a.REQUEST_ID , a.BUSINESS_UNIT_GL , a.BUSINESS_UNIT_RECV , a.RECEIVER_ID , a.RECV_LN_NBR , a.RECV_SHIP_SEQ_NBR , a.INV_ITEM_ID, a.FISCAL_YEAR , a.ACCOUNTING_PERIOD , a.ACCOUNT , a.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
4 REQUEST_ID Character(10) VARCHAR2(10) NOT NULL Request ID
5 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

Prompt Table: SP_BUPRCR_NONVW

6 BUSINESS_UNIT_RECV Character(5) VARCHAR2(5) NOT NULL Receiving Business Unit
7 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
8 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
9 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
10 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
11 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
12 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
13 ACCOUNTING_PERIOD Number(3,0) SMALLINT NOT NULL Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
14 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 MONETARY_AMT_VCHR Signed Number(28,3) DECIMAL(26,3) NOT NULL Monetary Amount - VCHR BU Base
17 MONETARY_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
18 DIFFERENCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Difference Amount