VIEW: APPS.GMS_DRAFT_REVENUES_V

Source

View Text - Preformatted

SELECT R.ROWID , AW.AWARD_ID , AW.AWARD_NUMBER , AW.AWARD_SHORT_NAME , P.SEGMENT1 , AW.AWARD_SHORT_NAME , PS.PROJECT_STATUS_NAME , R.DRAFT_REVENUE_NUM ,C.ACCOUNT_NUMBER ,SUBSTRB(PARTY.PARTY_NAME,1,50) , A.AGREEMENT_NUM , RI.REVPROC_CURRENCY_CODE , SUM(NVL(RI.AMOUNT, 0)) , LK.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 , R.GL_DATE , R.CUSTOMER_BILL_SPLIT , R.RESOURCE_ACCUMULATED_FLAG , R.UNBILLED_RECEIVABLE_DR , R.UNEARNED_REVENUE_CR , 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.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 , 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 , 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) , GMS_SECURITY.ALLOW_UPDATE(AW.AWARD_ID) , PT.CC_PRVDR_FLAG, ri.projfunc_currency_code , SUM(NVL(RI.PROJFUNC_REVENUE_AMOUNT, 0)) , 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 ,REALIZED_GAINS_AMOUNT ,REALIZED_LOSSES_AMOUNT ,REALIZED_GAINS_CCID ,REALIZED_LOSSES_CCID ,REALIZED_GAINS_BATCH_NAME ,REALIZED_LOSSES_BATCH_NAME ,R.EVENT_ID , R.REVTRANS_UBR_DR , R.REVTRANS_UER_CR , NVL(R.REVTRANS_CURRENCY_CODE, pa_currency.get_currency_code) , SUM(NVL(RI.REVTRANS_AMOUNT, RI.AMOUNT)) ,AW.ORG_ID , (SELECT '*' FROM PA_DISTRIBUTION_WARNINGS WHERE PROJECT_ID = P.PROJECT_ID AND DRAFT_REVENUE_NUM = R.DRAFT_REVENUE_NUM AND ROWNUM=1) 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 C ,PA_DRAFT_REVENUE_ITEMS RI ,PA_PROJECTS P ,GMS_AWARDS AW ,PA_DRAFT_REVENUES R ,PA_PROJECT_TYPES PT WHERE 'Y' in (select GMS_SECURITY.ALLOW_QUERY(AW.AWARD_ID) from sys.dual) AND P.PROJECT_ID = AW.AWARD_PROJECT_ID AND A.AGREEMENT_ID = AW.AGREEMENT_ID AND P.PROJECT_ID = R.PROJECT_ID 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 C.CUST_ACCOUNT_ID = A.CUSTOMER_ID AND C.PARTY_ID = PARTY.PARTY_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 P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE AND P.PROJECT_TYPE = PT.PROJECT_TYPE AND AW.ORG_ID = A.ORG_ID GROUP BY R.ROWID ,AW.AWARD_ID ,AW.AWARD_NUMBER ,AW.AWARD_SHORT_NAME ,P.SEGMENT1 ,AW.AWARD_SHORT_NAME , PS.PROJECT_STATUS_NAME ,R.DRAFT_REVENUE_NUM ,C.ACCOUNT_NUMBER ,SUBSTRB(PARTY.PARTY_NAME,1,50) ,A.AGREEMENT_NUM ,RI.REVPROC_CURRENCY_CODE ,LK.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 ,R.GL_DATE ,R.CUSTOMER_BILL_SPLIT ,R.RESOURCE_ACCUMULATED_FLAG ,R.UNBILLED_RECEIVABLE_DR ,R.UNEARNED_REVENUE_CR ,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.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 ,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 ,DECODE(R.TRANSFER_STATUS_CODE, 'R', 'Y', 'X', 'Y', 'N') ,P.TEMPLATE_FLAG ,PT.CC_PRVDR_FLAG ,RI.PROJFUNC_CURRENCY_CODE ,RI.PROJECT_CURRENCY_CODE ,RI.FUNDING_CURRENCY_CODE ,RI.REVPROC_CURRENCY_CODE , R.ADJUSTING_REVENUE_FLAG ,REALIZED_GAINS_AMOUNT ,REALIZED_LOSSES_AMOUNT ,REALIZED_GAINS_CCID ,REALIZED_LOSSES_CCID ,REALIZED_GAINS_BATCH_NAME ,REALIZED_LOSSES_BATCH_NAME ,EVENT_ID , R.REVTRANS_UBR_DR , R.REVTRANS_UER_CR , NVL(R.REVTRANS_CURRENCY_CODE, pa_currency.get_currency_code) ,AW.ORG_ID

View Text - HTML Formatted

SELECT R.ROWID
, AW.AWARD_ID
, AW.AWARD_NUMBER
, AW.AWARD_SHORT_NAME
, P.SEGMENT1
, AW.AWARD_SHORT_NAME
, PS.PROJECT_STATUS_NAME
, R.DRAFT_REVENUE_NUM
, C.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, A.AGREEMENT_NUM
, RI.REVPROC_CURRENCY_CODE
, SUM(NVL(RI.AMOUNT
, 0))
, LK.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
, R.GL_DATE
, R.CUSTOMER_BILL_SPLIT
, R.RESOURCE_ACCUMULATED_FLAG
, R.UNBILLED_RECEIVABLE_DR
, R.UNEARNED_REVENUE_CR
, 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.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
, 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
, 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)
, GMS_SECURITY.ALLOW_UPDATE(AW.AWARD_ID)
, PT.CC_PRVDR_FLAG
, RI.PROJFUNC_CURRENCY_CODE
, SUM(NVL(RI.PROJFUNC_REVENUE_AMOUNT
, 0))
, 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
, REALIZED_GAINS_AMOUNT
, REALIZED_LOSSES_AMOUNT
, REALIZED_GAINS_CCID
, REALIZED_LOSSES_CCID
, REALIZED_GAINS_BATCH_NAME
, REALIZED_LOSSES_BATCH_NAME
, R.EVENT_ID
, R.REVTRANS_UBR_DR
, R.REVTRANS_UER_CR
, NVL(R.REVTRANS_CURRENCY_CODE
, PA_CURRENCY.GET_CURRENCY_CODE)
, SUM(NVL(RI.REVTRANS_AMOUNT
, RI.AMOUNT))
, AW.ORG_ID
, (SELECT '*'
FROM PA_DISTRIBUTION_WARNINGS
WHERE PROJECT_ID = P.PROJECT_ID
AND DRAFT_REVENUE_NUM = R.DRAFT_REVENUE_NUM
AND ROWNUM=1)
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 C
, PA_DRAFT_REVENUE_ITEMS RI
, PA_PROJECTS P
, GMS_AWARDS AW
, PA_DRAFT_REVENUES R
, PA_PROJECT_TYPES PT
WHERE 'Y' IN (SELECT GMS_SECURITY.ALLOW_QUERY(AW.AWARD_ID)
FROM SYS.DUAL)
AND P.PROJECT_ID = AW.AWARD_PROJECT_ID
AND A.AGREEMENT_ID = AW.AGREEMENT_ID
AND P.PROJECT_ID = R.PROJECT_ID
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 C.CUST_ACCOUNT_ID = A.CUSTOMER_ID
AND C.PARTY_ID = PARTY.PARTY_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 P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND AW.ORG_ID = A.ORG_ID GROUP BY R.ROWID
, AW.AWARD_ID
, AW.AWARD_NUMBER
, AW.AWARD_SHORT_NAME
, P.SEGMENT1
, AW.AWARD_SHORT_NAME
, PS.PROJECT_STATUS_NAME
, R.DRAFT_REVENUE_NUM
, C.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, A.AGREEMENT_NUM
, RI.REVPROC_CURRENCY_CODE
, LK.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
, R.GL_DATE
, R.CUSTOMER_BILL_SPLIT
, R.RESOURCE_ACCUMULATED_FLAG
, R.UNBILLED_RECEIVABLE_DR
, R.UNEARNED_REVENUE_CR
, 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.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
, 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
, DECODE(R.TRANSFER_STATUS_CODE
, 'R'
, 'Y'
, 'X'
, 'Y'
, 'N')
, P.TEMPLATE_FLAG
, PT.CC_PRVDR_FLAG
, RI.PROJFUNC_CURRENCY_CODE
, RI.PROJECT_CURRENCY_CODE
, RI.FUNDING_CURRENCY_CODE
, RI.REVPROC_CURRENCY_CODE
, R.ADJUSTING_REVENUE_FLAG
, REALIZED_GAINS_AMOUNT
, REALIZED_LOSSES_AMOUNT
, REALIZED_GAINS_CCID
, REALIZED_LOSSES_CCID
, REALIZED_GAINS_BATCH_NAME
, REALIZED_LOSSES_BATCH_NAME
, EVENT_ID
, R.REVTRANS_UBR_DR
, R.REVTRANS_UER_CR
, NVL(R.REVTRANS_CURRENCY_CODE
, PA_CURRENCY.GET_CURRENCY_CODE)
, AW.ORG_ID