View: PA_DRAFT_REVENUES_FORM_MRC_V

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