VIEW: APPS.PA_CUST_REV_DIST_LINES_V

Source

View Text - Preformatted

SELECT V.ROW_ID , L.EXPENDITURE_ITEM_ID , V.EXPENDITURE_ID , L.PROJECT_ID , L.DRAFT_REVENUE_NUM , L.DRAFT_REVENUE_ITEM_LINE_NUM , V.TASK_ID , V.TASK_NUMBER , V.TASK_NAME , V.EXPENDITURE_ITEM_DATE , V.EXPENDITURE_TYPE , V.INCURRED_BY_PERSON_ID , V.EMPLOYEE_NAME , V.EMPLOYEE_NUMBER , V.JOB_ID , V.JOB_NAME , V.EXPENDITURE_ORGANIZATION_ID , V.EXPENDITURE_ORGANIZATION_NAME , V.VENDOR_ID , V.VENDOR_NAME , V.VENDOR_NUMBER , V.EMPLOYEE_VENDOR_NAME , V.EMPLOYEE_VENDOR_NUMBER , V.NON_LABOR_RESOURCE , V.NLR_ORGANIZATION_ID , V.NLR_ORGANIZATION_NAME , V.SYSTEM_LINKAGE_FUNCTION , V.SYSTEM_LINKAGE_M , V.EXPENDITURE_COMMENT , V.TRANSACTION_SOURCE , V.ORIG_TRANSACTION_REFERENCE , V.EXPENDITURE_GROUP , V.QUANTITY , V.UNIT_OF_MEASURE , V.UNIT_OF_MEASURE_M , DECODE(V.SYSTEM_LINKAGE_FUNCTION, 'ST', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', L.RAW_COST, NULL), 'OT', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', L.RAW_COST, NULL), L.RAW_COST) , V.PROJECT_CURRENCY_CODE , V.PROJECT_RATE_DATE , V.PROJECT_RATE_TYPE , V.USER_PROJECT_RATE_TYPE , V.PROJECT_EXCHANGE_RATE , DECODE(V.SYSTEM_LINKAGE_FUNCTION, 'ST', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.RECEIPT_CURRENCY_AMOUNT, NULL), 'OT', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.RECEIPT_CURRENCY_AMOUNT, NULL), V.RECEIPT_CURRENCY_AMOUNT) , V.RECEIPT_CURRENCY_CODE , V.RECEIPT_EXCHANGE_RATE , DECODE(V.SYSTEM_LINKAGE_FUNCTION, 'ST', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.DENOM_RAW_COST, NULL), 'OT', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.DENOM_RAW_COST, NULL), V.DENOM_RAW_COST) , V.DENOM_CURRENCY_CODE , DECODE(V.SYSTEM_LINKAGE_FUNCTION, 'ST', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.ACCT_RAW_COST, NULL), 'OT', DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID), 'Y', V.ACCT_RAW_COST, NULL), V.ACCT_RAW_COST) , V.ACCT_CURRENCY_CODE , V.ACCT_RATE_DATE , V.ACCT_RATE_TYPE , V.USER_ACCT_RATE_TYPE , V.ACCT_EXCHANGE_RATE , V.ACCT_EXCHANGE_ROUNDING_LIMIT , L.AMOUNT , ABS(L.AMOUNT/V.QUANTITY) , V.BILL_JOB_BILLING_TITLE , V.BILL_EMPLOYEE_BILLING_TITLE , V.ADJUSTED_EXPENDITURE_ITEM_ID , V.NET_ZERO_ADJUSTMENT_FLAG , V.CONVERTED_FLAG , V.SOURCE_EXPENDITURE_ITEM_ID , L.AMOUNT REVENUE_AMOUNT , L.CODE_COMBINATION_ID , L.FUNCTION_TRANSACTION_CODE , F.FUNCTION_TRANSACTION_NAME , DECODE(L.FUNCTION_CODE, 'LRL','*','LRB','*','URB','*','URL','*', NULL) , L.LINE_NUM , L.LINE_NUM_REVERSED , L.REVERSED_FLAG , V.COST_DISTRIBUTED_FLAG , V.RAW_COST_RATE , L.CREATION_DATE , L.CREATED_BY , L.BILL_TRANS_CURRENCY_CODE , L.BILL_TRANS_AMOUNT , L.BILL_TRANS_BILL_AMOUNT , L.BILL_RATE , L.REVPROC_CURRENCY_CODE , L.REVPROC_RATE_TYPE , L.REVPROC_RATE_DATE , L.REVPROC_EXCHANGE_RATE , C1.USER_CONVERSION_TYPE PROJECT_REV_RATE_TYPE , L.PROJECT_REV_RATE_DATE , L.PROJECT_REV_EXCHANGE_RATE , L.PROJECT_REVENUE_AMOUNT , L.PROJFUNC_CURRENCY_CODE , C2.USER_CONVERSION_TYPE PROJFUNC_REV_RATE_TYPE , L.PROJFUNC_REV_RATE_DATE , L.PROJFUNC_REV_EXCHANGE_RATE , L.FUNDING_CURRENCY_CODE , C3.USER_CONVERSION_TYPE FUNDING_REV_RATE_TYPE , L.FUNDING_REV_RATE_DATE , L.FUNDING_REV_EXCHANGE_RATE , L.FUNDING_REVENUE_AMOUNT ,l.retention_rule_id ,l.retn_draft_invoice_num ,l.retn_draft_invoice_line_num ,l.retained_amount , L.RATE_DISC_REASON_CODE , L.DISCOUNT_PERCENTAGE ,V.USER_TRANSACTION_SOURCE ,DECODE(L.REVTRANS_AMOUNT, null, decode(decode(P.multi_currency_billing_flag, 'Y', decode(P.inv_by_bill_trans_curr_flag, 'N', 'Y', 'N'), 'N'), 'Y',L.AMOUNT, null), L.REVTRANS_AMOUNT) ,NVL(L.REVTRANS_CURRENCY_CODE, pa_currency.get_currency_code) ,C4.USER_CONVERSION_TYPE REVPROC_REVTRANS_RATE_TYPE ,L.REVPROC_REVTRANS_RATE_DATE ,L.REVPROC_REVTRANS_EX_RATE FROM PA_CUST_REV_DIST_LINES_ALL L ,PA_FUNCTION_TRANSACTIONS F ,PA_EXPEND_ITEMS_ADJUST_V V ,PA_PROJECTS P ,PA_CONVERSION_TYPES_V C1 ,PA_CONVERSION_TYPES_V C2 ,PA_CONVERSION_TYPES_V C3 ,PA_CONVERSION_TYPES_V C4 WHERE L.FUNCTION_CODE = F.FUNCTION_CODE AND L.FUNCTION_TRANSACTION_CODE = F.FUNCTION_TRANSACTION_CODE AND F.APPLICATION_ID = 275 AND P.PROJECT_ID = L.PROJECT_ID AND L.EXPENDITURE_ITEM_ID = V.EXPENDITURE_ITEM_ID and l.project_rev_rate_type = c1.conversion_type(+) and l.projfunc_rev_rate_type = c2.conversion_type(+) and l.funding_rev_rate_type = c3.conversion_type(+) and l.revproc_revtrans_rate_type = c4.conversion_type(+)

View Text - HTML Formatted

SELECT V.ROW_ID
, L.EXPENDITURE_ITEM_ID
, V.EXPENDITURE_ID
, L.PROJECT_ID
, L.DRAFT_REVENUE_NUM
, L.DRAFT_REVENUE_ITEM_LINE_NUM
, V.TASK_ID
, V.TASK_NUMBER
, V.TASK_NAME
, V.EXPENDITURE_ITEM_DATE
, V.EXPENDITURE_TYPE
, V.INCURRED_BY_PERSON_ID
, V.EMPLOYEE_NAME
, V.EMPLOYEE_NUMBER
, V.JOB_ID
, V.JOB_NAME
, V.EXPENDITURE_ORGANIZATION_ID
, V.EXPENDITURE_ORGANIZATION_NAME
, V.VENDOR_ID
, V.VENDOR_NAME
, V.VENDOR_NUMBER
, V.EMPLOYEE_VENDOR_NAME
, V.EMPLOYEE_VENDOR_NUMBER
, V.NON_LABOR_RESOURCE
, V.NLR_ORGANIZATION_ID
, V.NLR_ORGANIZATION_NAME
, V.SYSTEM_LINKAGE_FUNCTION
, V.SYSTEM_LINKAGE_M
, V.EXPENDITURE_COMMENT
, V.TRANSACTION_SOURCE
, V.ORIG_TRANSACTION_REFERENCE
, V.EXPENDITURE_GROUP
, V.QUANTITY
, V.UNIT_OF_MEASURE
, V.UNIT_OF_MEASURE_M
, DECODE(V.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, L.RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, L.RAW_COST
, NULL)
, L.RAW_COST)
, V.PROJECT_CURRENCY_CODE
, V.PROJECT_RATE_DATE
, V.PROJECT_RATE_TYPE
, V.USER_PROJECT_RATE_TYPE
, V.PROJECT_EXCHANGE_RATE
, DECODE(V.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.RECEIPT_CURRENCY_AMOUNT
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.RECEIPT_CURRENCY_AMOUNT
, NULL)
, V.RECEIPT_CURRENCY_AMOUNT)
, V.RECEIPT_CURRENCY_CODE
, V.RECEIPT_EXCHANGE_RATE
, DECODE(V.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.DENOM_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.DENOM_RAW_COST
, NULL)
, V.DENOM_RAW_COST)
, V.DENOM_CURRENCY_CODE
, DECODE(V.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.ACCT_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS( L.PROJECT_ID)
, 'Y'
, V.ACCT_RAW_COST
, NULL)
, V.ACCT_RAW_COST)
, V.ACCT_CURRENCY_CODE
, V.ACCT_RATE_DATE
, V.ACCT_RATE_TYPE
, V.USER_ACCT_RATE_TYPE
, V.ACCT_EXCHANGE_RATE
, V.ACCT_EXCHANGE_ROUNDING_LIMIT
, L.AMOUNT
, ABS(L.AMOUNT/V.QUANTITY)
, V.BILL_JOB_BILLING_TITLE
, V.BILL_EMPLOYEE_BILLING_TITLE
, V.ADJUSTED_EXPENDITURE_ITEM_ID
, V.NET_ZERO_ADJUSTMENT_FLAG
, V.CONVERTED_FLAG
, V.SOURCE_EXPENDITURE_ITEM_ID
, L.AMOUNT REVENUE_AMOUNT
, L.CODE_COMBINATION_ID
, L.FUNCTION_TRANSACTION_CODE
, F.FUNCTION_TRANSACTION_NAME
, DECODE(L.FUNCTION_CODE
, 'LRL'
, '*'
, 'LRB'
, '*'
, 'URB'
, '*'
, 'URL'
, '*'
, NULL)
, L.LINE_NUM
, L.LINE_NUM_REVERSED
, L.REVERSED_FLAG
, V.COST_DISTRIBUTED_FLAG
, V.RAW_COST_RATE
, L.CREATION_DATE
, L.CREATED_BY
, L.BILL_TRANS_CURRENCY_CODE
, L.BILL_TRANS_AMOUNT
, L.BILL_TRANS_BILL_AMOUNT
, L.BILL_RATE
, L.REVPROC_CURRENCY_CODE
, L.REVPROC_RATE_TYPE
, L.REVPROC_RATE_DATE
, L.REVPROC_EXCHANGE_RATE
, C1.USER_CONVERSION_TYPE PROJECT_REV_RATE_TYPE
, L.PROJECT_REV_RATE_DATE
, L.PROJECT_REV_EXCHANGE_RATE
, L.PROJECT_REVENUE_AMOUNT
, L.PROJFUNC_CURRENCY_CODE
, C2.USER_CONVERSION_TYPE PROJFUNC_REV_RATE_TYPE
, L.PROJFUNC_REV_RATE_DATE
, L.PROJFUNC_REV_EXCHANGE_RATE
, L.FUNDING_CURRENCY_CODE
, C3.USER_CONVERSION_TYPE FUNDING_REV_RATE_TYPE
, L.FUNDING_REV_RATE_DATE
, L.FUNDING_REV_EXCHANGE_RATE
, L.FUNDING_REVENUE_AMOUNT
, L.RETENTION_RULE_ID
, L.RETN_DRAFT_INVOICE_NUM
, L.RETN_DRAFT_INVOICE_LINE_NUM
, L.RETAINED_AMOUNT
, L.RATE_DISC_REASON_CODE
, L.DISCOUNT_PERCENTAGE
, V.USER_TRANSACTION_SOURCE
, DECODE(L.REVTRANS_AMOUNT
, NULL
, DECODE(DECODE(P.MULTI_CURRENCY_BILLING_FLAG
, 'Y'
, DECODE(P.INV_BY_BILL_TRANS_CURR_FLAG
, 'N'
, 'Y'
, 'N')
, 'N')
, 'Y'
, L.AMOUNT
, NULL)
, L.REVTRANS_AMOUNT)
, NVL(L.REVTRANS_CURRENCY_CODE
, PA_CURRENCY.GET_CURRENCY_CODE)
, C4.USER_CONVERSION_TYPE REVPROC_REVTRANS_RATE_TYPE
, L.REVPROC_REVTRANS_RATE_DATE
, L.REVPROC_REVTRANS_EX_RATE
FROM PA_CUST_REV_DIST_LINES_ALL L
, PA_FUNCTION_TRANSACTIONS F
, PA_EXPEND_ITEMS_ADJUST_V V
, PA_PROJECTS P
, PA_CONVERSION_TYPES_V C1
, PA_CONVERSION_TYPES_V C2
, PA_CONVERSION_TYPES_V C3
, PA_CONVERSION_TYPES_V C4
WHERE L.FUNCTION_CODE = F.FUNCTION_CODE
AND L.FUNCTION_TRANSACTION_CODE = F.FUNCTION_TRANSACTION_CODE
AND F.APPLICATION_ID = 275
AND P.PROJECT_ID = L.PROJECT_ID
AND L.EXPENDITURE_ITEM_ID = V.EXPENDITURE_ITEM_ID
AND L.PROJECT_REV_RATE_TYPE = C1.CONVERSION_TYPE(+)
AND L.PROJFUNC_REV_RATE_TYPE = C2.CONVERSION_TYPE(+)
AND L.FUNDING_REV_RATE_TYPE = C3.CONVERSION_TYPE(+)
AND L.REVPROC_REVTRANS_RATE_TYPE = C4.CONVERSION_TYPE(+)