eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PA_DRAFT_REVENUES_FORM_MRC_V

Product:	PA - Projects
Description:	This will select data from pa_draft_revenues, pa_mc_draft_revs_all, pa_draft_revenue_items, pa_agreements_all
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT R.ROWID
, MRC_R.SET_OF_BOOKS_ID
, P.SEGMENT1
, P.NAME
, PS.PROJECT_STATUS_NAME
, R.DRAFT_REVENUE_NUM
, CUST_ACCT.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, A.AGREEMENT_NUM
, RI.REVPROC_CURRENCY_CODE
, GL_MC_CURRENCY_PKG.GET_CURRENCY_CODE(NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99))
, SUM(NVL(RI.AMOUNT
, 0))
, LK.MEANING
, LK3.MEANING
, R.DRAFT_REVENUE_NUM_CREDITED
, R.ACCRUE_THROUGH_DATE
, DECODE(R.GENERATION_ERROR_FLAG
, 'Y'
, 'ERROR'
, DECODE(R.RELEASED_DATE
, ''
, 'UNRELEASED'
, DECODE(R.TRANSFER_STATUS_CODE
, 'X'
, 'ERROR'
, 'R'
, 'ERROR'
, 'OTHER')) )
, R.PA_DATE
, R.RELEASED_DATE
, R.TRANSFERRED_DATE
, MRC_R.TRANSFERRED_DATE
, R.GL_DATE
, R.CUSTOMER_BILL_SPLIT
, R.RESOURCE_ACCUMULATED_FLAG
, R.UNBILLED_RECEIVABLE_DR
, MRC_R.UNBILLED_RECEIVABLE_DR
, R.UNEARNED_REVENUE_CR
, MRC_R.UNEARNED_REVENUE_CR
, R.TRANSFER_REJECTION_REASON
, MRC_R.TRANSFER_REJECTION_REASON
, R.UNBILLED_CODE_COMBINATION_ID
, R.UNEARNED_CODE_COMBINATION_ID
, R.UNBILLED_BATCH_NAME
, R.UNEARNED_BATCH_NAME
, R.ACCUMULATED_FLAG
, P.PROJECT_ID
, A.CUSTOMER_ID
, A.AGREEMENT_ID
, R.TRANSFER_STATUS_CODE
, R.GENERATION_ERROR_FLAG
, P.PROJECT_TYPE
, P.PROJECT_STATUS_CODE
, ORG.NAME
, LK.LOOKUP_CODE
, LK3.LOOKUP_CODE
, ORG.ORGANIZATION_ID
, R.REQUEST_ID
, R.LAST_UPDATE_DATE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, R.LAST_UPDATE_LOGIN
, R.PROGRAM_APPLICATION_ID
, R.PROGRAM_ID
, R.PROGRAM_UPDATE_DATE
, LK2.MEANING
, LK3.MEANING
, DECODE(R.TRANSFER_STATUS_CODE
, 'R'
, 'Y'
, 'X'
, 'Y'
, 'N')
, PA_SECURITY.ALLOW_UPDATE(P.PROJECT_ID)
, PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, PT.CC_PRVDR_FLAG
, RI.PROJFUNC_CURRENCY_CODE
, GL_MC_CURRENCY_PKG.GET_CURRENCY_CODE(NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99))
, SUM(NVL(RI.PROJFUNC_REVENUE_AMOUNT
, 0))
, SUM(PA_MC_CURRENCY_PKG.SUM_MC_CUST_RDL_ERDL(RI.PROJECT_ID
, RI.DRAFT_REVENUE_NUM
, RI.LINE_NUM))
, RI.PROJECT_CURRENCY_CODE
, SUM(NVL(RI.PROJECT_REVENUE_AMOUNT
, 0))
, RI.FUNDING_CURRENCY_CODE
, SUM(NVL(RI.FUNDING_REVENUE_AMOUNT
, 0))
, RI.REVPROC_CURRENCY_CODE
, R.ADJUSTING_REVENUE_FLAG
, R.REALIZED_GAINS_AMOUNT
, MRC_R.REALIZED_GAINS_AMOUNT
, R.REALIZED_LOSSES_AMOUNT
, MRC_R.REALIZED_LOSSES_AMOUNT
, R.REALIZED_GAINS_CCID
, R.REALIZED_LOSSES_CCID
, R.REALIZED_GAINS_BATCH_NAME
, R.REALIZED_LOSSES_BATCH_NAME
, R.EVENT_ID
, R.REVTRANS_CURRENCY_CODE
, R.REVTRANS_UBR_DR
, R.REVTRANS_UER_CR
, SUM(NVL(RI.REVTRANS_AMOUNT
, RI.AMOUNT))
FROM PA_LOOKUPS LK
, PA_LOOKUPS LK2
, PA_PROJECT_STATUSES PS
, HR_ORGANIZATION_UNITS ORG
, PA_AGREEMENTS_ALL A
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, PA_DRAFT_REVENUE_ITEMS RI
, PA_PROJECTS P
, PA_DRAFT_REVENUES_ALL R
, PA_MC_DRAFT_REVS_ALL MRC_R
, PA_PROJECT_TYPES_ALL PT
, PA_LOOKUPS LK3
WHERE 'Y' IN (SELECT PA_SECURITY.ALLOW_QUERY(P.PROJECT_ID)
FROM SYS.DUAL)
AND P.PROJECT_ID = R.PROJECT_ID
AND P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE
AND PS.STATUS_TYPE = 'PROJECT'
AND R.AGREEMENT_ID = A.AGREEMENT_ID
AND RI.PROJECT_ID = R.PROJECT_ID
AND RI.DRAFT_REVENUE_NUM = R.DRAFT_REVENUE_NUM
AND P.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = A.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.ORG_ID = P.ORG_ID
AND LK.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS'
AND LK.LOOKUP_CODE = DECODE(R.GENERATION_ERROR_FLAG
, 'Y'
, 'GENERATION ERROR'
, DECODE(R.RELEASED_DATE
, NULL
, 'UNRELEASED'
, DECODE(R.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED'
, 'RECEIVED')))
AND LK2.LOOKUP_TYPE = 'TRANSFER STATUS'
AND LK2.LOOKUP_CODE = R.TRANSFER_STATUS_CODE
AND LK3.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS'
AND LK3.LOOKUP_CODE = DECODE(R.GENERATION_ERROR_FLAG
, 'Y'
, 'GENERATION ERROR'
, DECODE(R.RELEASED_DATE
, NULL
, 'UNRELEASED'
, DECODE(MRC_R.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED'
, 'RECEIVED')))
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND P.ORG_ID = PT.ORG_ID
AND MRC_R.DRAFT_REVENUE_NUM = R.DRAFT_REVENUE_NUM
AND MRC_R.PROJECT_ID = R.PROJECT_ID
AND MRC_R.SET_OF_BOOKS_ID = NVL( TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10))
, -99) GROUP BY R.ROWID
, P.SEGMENT1
, P.NAME
, PS.PROJECT_STATUS_NAME
, R.DRAFT_REVENUE_NUM
, CUST_ACCT.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, A.AGREEMENT_NUM
, LK.MEANING
, LK3.MEANING
, R.DRAFT_REVENUE_NUM_CREDITED
, R.ACCRUE_THROUGH_DATE
, DECODE(R.GENERATION_ERROR_FLAG
, 'Y'
, 'ERROR'
, DECODE(R.RELEASED_DATE
, ''
, 'UNRELEASED'
, DECODE(R.TRANSFER_STATUS_CODE
, 'X'
, 'ERROR'
, 'R'
, 'ERROR'
, 'OTHER')))
, R.PA_DATE
, R.RELEASED_DATE
, R.TRANSFERRED_DATE
, MRC_R.TRANSFERRED_DATE
, R.GL_DATE
, R.CUSTOMER_BILL_SPLIT
, R.RESOURCE_ACCUMULATED_FLAG
, R.UNBILLED_RECEIVABLE_DR
, MRC_R.UNBILLED_RECEIVABLE_DR
, R.UNEARNED_REVENUE_CR
, MRC_R.UNEARNED_REVENUE_CR
, R.TRANSFER_REJECTION_REASON
, MRC_R.TRANSFER_REJECTION_REASON
, R.UNBILLED_CODE_COMBINATION_ID
, R.UNEARNED_CODE_COMBINATION_ID
, R.UNBILLED_BATCH_NAME
, R.UNEARNED_BATCH_NAME
, R.ACCUMULATED_FLAG
, P.PROJECT_ID
, A.CUSTOMER_ID
, A.AGREEMENT_ID
, R.TRANSFER_STATUS_CODE
, R.GENERATION_ERROR_FLAG
, P.PROJECT_TYPE
, P.PROJECT_STATUS_CODE
, ORG.NAME
, LK.LOOKUP_CODE
, LK3.LOOKUP_CODE
, ORG.ORGANIZATION_ID
, R.REQUEST_ID
, R.LAST_UPDATE_DATE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, R.LAST_UPDATE_LOGIN
, R.PROGRAM_APPLICATION_ID
, R.PROGRAM_ID
, R.PROGRAM_UPDATE_DATE
, LK2.MEANING
, LK3.MEANING
, DECODE(R.TRANSFER_STATUS_CODE
, 'R'
, 'Y'
, 'X'
, 'Y'
, 'N')
, P.TEMPLATE_FLAG
, PT.CC_PRVDR_FLAG
, RI.PROJFUNC_CURRENCY_CODE
, GL_MC_CURRENCY_PKG.GET_CURRENCY_CODE(NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99))
, RI.PROJECT_CURRENCY_CODE
, RI.FUNDING_CURRENCY_CODE
, RI.REVPROC_CURRENCY_CODE
, GL_MC_CURRENCY_PKG.GET_CURRENCY_CODE(NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99))
, R.ADJUSTING_REVENUE_FLAG
, R.REALIZED_GAINS_AMOUNT
, MRC_R.REALIZED_GAINS_AMOUNT
, R.REALIZED_LOSSES_AMOUNT
, MRC_R.REALIZED_LOSSES_AMOUNT
, R.REALIZED_GAINS_CCID
, R.REALIZED_LOSSES_CCID
, R.REALIZED_GAINS_BATCH_NAME
, R.REALIZED_LOSSES_BATCH_NAME
, MRC_R.SET_OF_BOOKS_ID
, R.EVENT_ID
, R.REVTRANS_CURRENCY_CODE
, R.REVTRANS_UBR_DR
, R.REVTRANS_UER_CR

Columns

Name
ROW_ID
SET_OF_BOOKS_ID
PROJECT_NUMBER
PROJECT_NAME
PROJECT_STATUS
DRAFT_REVENUE_NUM
CUSTOMER_NUMBER
CUSTOMER_NAME
AGREEMENT_NUM
REVENUE_CURRENCY_CODE
REVENUE_CURRENCY_CODE_MRC
REVENUE_AMOUNT
REVENUE_STATUS
REVENUE_STATUS_MRC
DRAFT_REVENUE_NUM_CREDITED
ACCRUE_THROUGH_DATE
GENERATION_ERROR
PA_DATE
RELEASED_DATE
TRANSFERRED_DATE
TRANSFERRED_DATE_MRC
GL_DATE
CUSTOMER_BILL_SPLIT
RESOURCE_ACCUMULATED_FLAG
UNBILLED_RECEIVABLE_DR
UNBILLED_RECEIVABLE_DR_MRC
UNEARNED_REVENUE_CR
UNEARNED_REVENUE_CR_MRC
TRANSFER_REJECTION_REASON
TRANSFER_REJECTION_REASON_MRC
UNBILLED_CODE_COMBINATION_ID
UNEARNED_CODE_COMBINATION_ID
UNBILLED_BATCH_NAME
UNEARNED_BATCH_NAME
ACCUMULATED_FLAG
PROJECT_ID
CUSTOMER_ID
AGREEMENT_ID
TRANSFER_STATUS_CODE
GENERATION_ERROR_FLAG
PROJECT_TYPE
PROJECT_STATUS_CODE
PROJECT_ORGANIZATION_NAME
REVENUE_STATUS_CODE
REVENUE_STATUS_CODE_MRC
PROJECT_ORGANIZATION_ID
REQUEST_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
TRANSFER_STATUS
TRANSFER_STATUS_MRC
EXCEPTION_FLAG
UPDATE_ALLOWED
VIEW_LABOR_COSTS_ALLOWED
CC_PRVDR_FLAG
PROJFUNC_CURRENCY_CODE
PROJFUNC_CURRENCY_CODE_MRC
PROJFUNC_REVENUE_AMOUNT
PROJFUNC_REVENUE_AMOUNT_MRC
PROJECT_CURRENCY_CODE
PROJECT_REVENUE_AMOUNT
FUNDING_CURRENCY_CODE
FUNDING_REVENUE_AMOUNT
REVPROC_CURRENCY_CODE
ADJUSTING_REVENUE_FLAG
REALIZED_GAINS_AMOUNT
REALIZED_GAINS_AMOUNT_MRC
REALIZED_LOSSES_AMOUNT
REALIZED_LOSSES_AMOUNT_MRC
REALIZED_GAINS_CCID
REALIZED_LOSSES_CCID
REALIZED_GAINS_BATCH_NAME
REALIZED_LOSSES_BATCH_NAME
EVENT_ID
REVTRANS_CURRENCY_CODE
REVTRANS_UBR_DR
REVTRANS_UER_CR
REVTRANS_AMOUNT