GM_LOC_EXP_VW

(SQL View)
 Index Back

LOC Expiration View

LOC Expiration View Pivot Grid

SELECT PREF.OPRID ,PREF.BUSINESS_UNIT ,PREF.CONTRACT_NUM ,DTL.CONTRACT_LINE_NUM ,PREF.TITLE56 ,PREF.NAME ,PREF.NAME1 ,HDR.LOC_REFERENCE_ID ,DTL.LOC_DOC_ID ,DTL.SUB_ACCOUNT_NUM ,DTL.LOC_DOC_ID_END_DT ,LMT.LIMIT_AMT_BIL ,LMT.LMT_PROC_AMT_B ,(LMT.LIMIT_AMT_BIL - LMT.LMT_PROC_AMT_B) ,HDR.CURRENCY_CD , CASE WHEN (DTL.LOC_DOC_ID_END_DT < %CurrentDateIn) THEN '0' WHEN (%DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT)<=30 AND %DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT) >=0) THEN '1' WHEN (%DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT)<=60 AND %DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT) >=31) THEN '2' WHEN (%DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT)<=90 AND %DateDiff(%CurrentDateIn,DTL.LOC_DOC_ID_END_DT) >=61) THEN '3' END FROM PS_GM_PREF_AWD_VW PREF , PS_CA_CONTR_HDR HDR , PS_CA_DETAIL DTL , PS_CA_LMT_FEE_DTL LMT WHERE PREF.CONTRACT_NUM=HDR.CONTRACT_NUM AND HDR.CONTRACT_NUM=DTL.CONTRACT_NUM AND DTL.CONTRACT_NUM=LMT.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM=LMT.CONTRACT_LINE_NUM AND HDR.LOC_REFERENCE_ID <> ' ' AND DTL.LOC_DOC_INACTIVE ='N' AND DTL.LOC_DOC_ID <> ' ' AND DTL.LOC_DOC_ID_END_DT IS NOT NULL AND DTL.LOC_DOC_ID_END_DT <= (%DateAdd(%CurrentDateIn,90)) AND (LMT.LIMIT_AMT_BIL - LMT.LMT_PROC_AMT_B)>0

  • Related Language Record: GM_PREF_AWD_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Award ID
    4 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
    5 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
    6 NAME Character(50) VARCHAR2(50) NOT NULL Name
    7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    8 LOC_REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Letter of Credit ID
    9 LOC_DOC_ID Character(30) VARCHAR2(30) NOT NULL Letter of Credit Document ID
    10 SUB_ACCOUNT_NUM Character(50) VARCHAR2(50) NOT NULL Subaccount ID
    11 LOC_DOC_ID_END_DT Date(10) DATE LOC Doc ID End Date
    12 LIMIT_AMT_BIL Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded/Billing Limit Amount
    13 LMT_PROC_AMT_B Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded / Billing Processed Amount
    14 P_AVAILABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Available Amount
    15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    16 GM_DUE_INDICATOR Character(1) VARCHAR2(1) NOT NULL Due Indicator
    0=Overdue
    1=0-30
    2=31-60
    3=61-90