CA_LOC_AMT_VW(SQL View) |
Index Back |
|---|---|
Amounts by LOC Doc IDAmounts by LOC Doc ID |
| SELECT HDR.BUSINESS_UNIT , DTL.LOC_DOC_ID , DTL.LOC_DOC_ID_END_DT , DTL.SUB_ACCOUNT_NUM , CU.CUST_ID , CU.NAME1 , BP.LOC_REFERENCE_ID , SP.DESCR , HDR.CONTRACT_NUM , DTL.CONTRACT_LINE_NUM , BP.BILL_PLAN_ID , BP.BP_STATUS , BP.HOLD_FLAG , LMT.LIMIT_AMT_BIL , %Coalesce((SELECT SUM(PR.RESOURCE_AMOUNT) FROM PS_CA_DETAIL_PROJ CPR , PS_PROJ_RESOURCE PR WHERE CPR.CONTRACT_NUM = DTL.CONTRACT_NUM AND CPR.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM AND CPR.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CPR.PROJECT_ID = PR.PROJECT_ID AND CPR.ACTIVITY_ID = PR.ACTIVITY_ID AND PR.ANALYSIS_TYPE = 'BLD' AND HDR.CURRENCY_CD = PR.CURRENCY_CD), 0) , %Coalesce((SELECT SUM(PR.RESOURCE_AMOUNT) FROM PS_CA_DETAIL_PROJ CPR , PS_PROJ_RESOURCE PR WHERE CPR.CONTRACT_NUM = DTL.CONTRACT_NUM AND CPR.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM AND CPR.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CPR.PROJECT_ID = PR.PROJECT_ID AND CPR.ACTIVITY_ID = PR.ACTIVITY_ID AND PR.ANALYSIS_TYPE = 'BIL' AND HDR.CURRENCY_CD = PR.CURRENCY_CD), 0) , %Coalesce((SELECT SUM(PR.RESOURCE_AMOUNT) FROM PS_CA_DETAIL_PROJ CPR , PS_PROJ_RESOURCE PR WHERE CPR.CONTRACT_NUM = DTL.CONTRACT_NUM AND CPR.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM AND CPR.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CPR.PROJECT_ID = PR.PROJECT_ID AND CPR.ACTIVITY_ID = PR.ACTIVITY_ID AND PR.ANALYSIS_TYPE = 'DEF' AND HDR.CURRENCY_CD = PR.CURRENCY_CD), 0) , %Coalesce((SELECT SUM(PR.RESOURCE_AMOUNT) FROM PS_CA_DETAIL_PROJ CPR , PS_PROJ_RESOURCE PR WHERE CPR.CONTRACT_NUM = DTL.CONTRACT_NUM AND CPR.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM AND CPR.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CPR.PROJECT_ID = PR.PROJECT_ID AND CPR.ACTIVITY_ID = PR.ACTIVITY_ID AND PR.ANALYSIS_TYPE = 'OLT' AND HDR.CURRENCY_CD = PR.CURRENCY_CD), 0) , %DateDiff(%CurrentDateIn, DTL.LOC_DOC_ID_END_DT) , HDR.CURRENCY_CD FROM PS_CA_CONTR_HDR HDR , PS_CA_DETAIL DTL , PS_GM_SPNSR_LOC SP , PS_CA_BILL_PLAN BP , PS_CA_LMT_FEE_DTL LMT , PS_SET_CNTRL_REC SC , PS_CUSTOMER CU WHERE HDR.CONTRACT_NUM = DTL.CONTRACT_NUM AND BP.CONTRACT_NUM = DTL.CONTRACT_NUM AND BP.BILL_PLAN_ID = DTL.BILL_PLAN_ID AND LMT.CONTRACT_NUM = DTL.CONTRACT_NUM AND LMT.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM AND SC.SETCNTRLVALUE = HDR.BUSINESS_UNIT AND SC.RECNAME = 'CUSTOMER' AND CU.SETID = SC.SETID AND CU.CUST_ID = HDR.SOLD_TO_CUST_ID AND SP.SETID = SC.SETID AND SP.CUST_ID = HDR.SOLD_TO_CUST_ID AND SP.LOC_REFERENCE_ID = BP.LOC_REFERENCE_ID AND DTL.LOC_DOC_ID <> ' ' AND HDR.CA_STATUS = 'ACTIVE' AND DTL.LOC_DOC_INACTIVE = 'N' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | LOC_DOC_ID | Character(30) | VARCHAR2(30) NOT NULL | Letter of Credit Document ID |
| 3 | LOC_DOC_ID_END_DT | Date(10) | DATE | LOC Doc ID End Date |
| 4 | SUB_ACCOUNT_NUM | Character(50) | VARCHAR2(50) NOT NULL | Subaccount ID |
| 5 | 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. |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | LOC_REFERENCE_ID | Character(10) | VARCHAR2(10) NOT NULL |
Letter of Credit ID
Prompt Table:
GM_SPNSR_LC2_VW
|
| 8 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 9 |  CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL |
Contract
Prompt Table: CA_CNTR_SRCH_VW |
| 10 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 11 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 12 | BP_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Billing Plan Status
CAN=Cancelled DON=Completed PND=Pending PRG=In Progress RCL=Recycled RDY=Ready RIP=Reversal In Progress RQD=Action Required RRP=Recycled RVS=Reversed |
| 13 | HOLD_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Hold
N=Hold Y=Hold |
| 14 | LIMIT_AMT_BIL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Funded/Billing Limit Amount |
| 15 | BILLED_AMT_CA | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Billed Amount |
| 16 | UNBILLED_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Unbilled Amount |
| 17 | DEFERRED_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Deferred Amount |
| 18 | OVER_LIMIT_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Over the Limit Amount |
| 19 | DAYS_REMAINING | Number(4,0) | SMALLINT NOT NULL | Days To |
| 20 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |