PO_PERF_PUSH_VW

(SQL View)
 Index Back

Push Performance Data

Date Initials Reference# Description 02/28/2021 Nash G-Invoicing Created a new view that helps to bring the eligible Performance Data for PUSH .

SELECT CASE WHEN EXISTS( SELECT 'X' FROM PS_RECV_PUSHED_GNV G WHERE G.BUSINESS_UNIT = R.BUSINESS_UNIT_PO AND G.RECEIVER_ID = R.RECEIVER_ID AND G.SHIPMENT_NO = R.SHIPMENT_NO AND G.RECV_LN_NBR = R.RECV_LN_NBR) THEN ( SELECT G.GNV_PERFRM_REF_NBR FROM PS_RECV_PUSHED_GNV G WHERE G.BUSINESS_UNIT = R.BUSINESS_UNIT_PO AND G.RECEIVER_ID = R.RECEIVER_ID AND G.SHIPMENT_NO = R.SHIPMENT_NO AND G.RECV_LN_NBR = R.RECV_LN_NBR) ELSE H.GNV_PERFORM_NBR END ,H.GNV_ORDER_NUM ,R.RECEIPT_DTTM ,H.GNV_ACCOUNT_PERIOD, H.GNV_PREPAREDBY_NAM , H.GNV_PREPARE_PHONE, H.GNV_PREPAREDB_MAIL,'050' ,'R' ,R.RECV_LN_NBR ,R.GNV_PRFORM_DTL_NBR ,R.GNV_ORDER_LINE_NBR ,R.GNV_SCHED_NBR ,CASE WHEN EXISTS( SELECT 'X' FROM PS_RECV_PUSHED_GNV G WHERE G.BUSINESS_UNIT = R.BUSINESS_UNIT_PO AND G.RECEIVER_ID = R.RECEIVER_ID AND G.SHIPMENT_NO = R.SHIPMENT_NO AND G.RECV_LN_NBR = R.RECV_LN_NBR) THEN ( SELECT R.CURR_ACCPT_QTY - G.GNV_QTY_TTL FROM PS_RECV_PUSHED_GNV G WHERE G.BUSINESS_UNIT = R.BUSINESS_UNIT_PO AND G.RECEIVER_ID = R.RECEIVER_ID AND G.SHIPMENT_NO = R.SHIPMENT_NO AND G.RECV_LN_NBR = R.RECV_LN_NBR) ELSE R.CURR_ACCPT_QTY END ,' ' ,R.BUSINESS_UNIT_PO ,R.SHIPMENT_NO, ' ', R.RECEIVER_ID ,R.RECV_LN_NBR ,R.CURR_ACCPT_QTY ,R.RECEIPT_DT FROM PS_PO_GNV_PER_HDR H , PS_RECV_GNV_VW R WHERE H.BUSINESS_UNIT_PO = R.BUSINESS_UNIT_PO AND H.GNV_ORDER_NUM = R.GNV_ORDER_NUM AND H.GNV_PERFORM_NBR = R.SHIPMENT_NO AND H.PO_ID = R.PO_ID AND H.RECEIVER_ID = R.RECEIVER_ID AND H.GNV_PERFORM_TYPE = '035' AND NOT EXISTS ( SELECT 'X' FROM PS_RECV_PUSHED_GNV G1 WHERE G1.BUSINESS_UNIT = R.BUSINESS_UNIT_PO AND G1.RECEIVER_ID = R.RECEIVER_ID AND G1.SHIPMENT_NO = R.SHIPMENT_NO AND G1.RECV_LN_NBR = R.RECV_LN_NBR AND (R.CURR_ACCPT_QTY - G1.GNV_QTY_TTL) = 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GNV_PERFRM_REF_NBR Character(20) VARCHAR2(20) NOT NULL A number that links a new Performance Transaction to an existing one. G-INVOICING MANINSRI
2 GNV_ORDER_NUM Character(20) VARCHAR2(20) NOT NULL G-INVOICING MANINSRI 15/06/2021
3 GNV_PERF_DTTM DateTime(26) TIMESTAMP The date and time the performance transaction was received by a system, e.g G-Invoicing. MANINSRI 16/06/2021
4 GNV_ACCOUNT_PERIOD Number(6,0) INTEGER NOT NULL The fiscal period that the transaction is reflected in the General Ledger, if applicable. G-INVOICING MANINSRI 16/06/2021
5 GNV_PREPAREDBY_NAM Character(60) VARCHAR2(60) NOT NULL The full name of the user submitting the performance transaction. G-INVOICING MANINSRI
6 GNV_PREPARE_PHONE Character(20) VARCHAR2(20) NOT NULL A number assigned to a telephone line for a specific location that is used to call that location. G-Invoicing MANINSRI 06/06/2021
7 GNV_PREPAREDB_MAIL Character(60) VARCHAR2(60) NOT NULL The e-mail address of the user submitting the performance transaction. G-INVOICING MANINSRI 16/06/2021
8 GNV_PERFORM_TYPE Character(3) VARCHAR2(3) NOT NULL Identifies the nature of the performance in the reported transaction. G-INVOICING MANINSRI 16/06/2021
014=Deferred Payment
035=Delivered/Performed
050=Received/Accepted
548=Advance
9 GNV_BUY_SELL_IND Character(1) VARCHAR2(1) NOT NULL Designates whether the Performance transaction or Attachment originated from the Requesting or Servicing agency. G-INVOICING MANINSRI 16/06/2021
R=Buyer
S=Seller
10 GNV_PRFORM_DTL_NBR Number(6,0) INTEGER NOT NULL Uniquely identifies a specific detail record within the performance transaction. (Agency supplied). G_INVOICING MANINSRI
11 GNV_PERF_DTL_REF_N Number(6,0) INTEGER NOT NULL A number that links a new Performance Detail to an existing one. G_INVOICING MANINSRI 17/06/2021
12 GNV_ORDER_LINE_NBR Number(5,0) INTEGER NOT NULL Identifier of a specific Line Item within the Order. G_INVOICING MANINSRI 17/06/2021
13 GNV_SCHED_NBR Number(5,0) INTEGER NOT NULL Schedule Number
14 GNV_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL The number of units pertaining to the type of performance being reported. G_INVOICING MANINSRI 17/06/2021
15 GNV_ATTACH_NAME Character(132) VARCHAR2(132) NOT NULL The actual name of the attachment file. G-INVOICING MANINSRI 16/06/2021
16 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
17 SHIPMENT_NO Character(30) VARCHAR2(30) NOT NULL Shipment Number
18 GNV_PERFORM_NBR Character(20) VARCHAR2(20) NOT NULL A number for a Performance transaction, assigned by G-Invoicing (GINV) at the time the transaction is recorded in GINV. MANINSRI 16/06/2021
19 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
20 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
21 CURR_ACCPT_QTY Number(16,4) DECIMAL(15,4) NOT NULL Current Accept Receipt Qty
22 RECEIPT_DT Date(10) DATE Received Date