VIEW: APPS.PA_TRX_FUNDS_CHK_MAIN_V

Source

View Text - Preformatted

SELECT BC.ROWID ROW_ID ,BC.PACKET_ID PACKET_ID ,BC.PROJECT_ID PROJECT_ID ,BC.TASK_ID TASK_ID ,BC.TOP_TASK_ID TOP_TASK_ID ,BC.EXPENDITURE_TYPE EXPENDITURE_TYPE ,PET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY ,BC.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE ,BC.GL_DATE GL_DATE ,BC.ACTUAL_FLAG ACTUAL_FLAG ,bc.status_code STATUS_CODE ,L1.MEANING STATUS_MEANING ,BC.JE_CATEGORY_NAME JE_CATEGORY_NAME ,BC.JE_SOURCE_NAME JE_SOURCE_NAME ,BC.DOCUMENT_TYPE DOC_TYPE_CODE ,substr(L2.MEANING,1,50) DOCUMENT_TYPE ,BC.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID ,BC.PERIOD_NAME PERIOD_NAME ,BC.PERIOD_YEAR PERIOD_YEAR ,BC.PERIOD_NUM PERIOD_NUM ,BC.DOCUMENT_HEADER_ID DOCUMENT_HEADER_ID ,BC.DOCUMENT_DISTRIBUTION_ID DOCUMENT_DISTRIBUTION_ID ,BC.BUDGET_VERSION_ID BUDGET_VERSION_ID ,NVL(BC.accounted_dr,0)-NVL(BC.accounted_cr,0) AMOUNT ,BC.EFFECT_ON_FUNDS_CODE EFFECT_ON_FUNDS_CODE ,BC.RESULT_CODE RESULT_CODE ,BC.BALANCE_POSTED_FLAG BALANCE_POSTED_FLAG ,PR.SEGMENT1 PROJECT ,TA.TASK_NUMBER TASK ,TOP_TA.TASK_NUMBER TOP_TASK ,(select resg.name from pa_resources resg ,pa_resource_list_members rlmi where rlmi.resource_list_member_id = bc.resource_list_member_id and rlmi.resource_id = resg.resource_id) RESOURCE_NAME ,(select resg.name from pa_resources resg ,pa_resource_list_members rlmi where rlmi.resource_list_member_id = nvl(BC.PARENT_RESOURCE_ID,BC.resource_list_member_id) and rlmi.parent_member_id is null and rlmi.resource_id = resg.resource_id) RESOURCE_GROUP_NAME ,substr(pa_expenditures_utils.getorgtlname(BC.EXPENDITURE_ORGANIZATION_ID),1,30) EXP_ORG_NAME ,GBV.VERSION_NAME VERSION_NAME ,GBV.VERSION_NUMBER VERSION_NUMBER ,L3.MEANING BUDGET_STATUS_CODE ,PBT.BUDGET_TYPE BUDGET_TYPE ,BC.BC_PACKET_ID BC_PACKET_ID ,BC.res_result_code RES_RESULT_CODE ,BC.res_grp_result_code RES_GRP_RESULT_CODE ,BC.task_result_code TASK_RESULT_CODE ,BC.top_task_result_code TOP_TASK_RESULT_CODE ,BC.project_result_code PROJECT_RESULT_CODE ,BC.project_acct_result_code PROJECT_ACCT_RESULT_CODE ,NVL(BC.parent_resource_id,0) PARENT_RESOURCE_ID ,BC.resource_list_member_id RESOURCE_LIST_MEMBER_ID ,BC.fc_start_date START_DATE ,BC.fc_end_date END_DATE ,0 RES_ENC_PENDING ,0 RES_GRP_ENC_PENDING ,0 TASK_ENC_PENDING ,0 TOP_TASK_ENC_PENDING ,0 PROJECT_ENC_PENDING ,0 PROJECT_ACCT_ENC_PENDING ,BC.BUDGET_CCID BUDGET_CCID ,BC.DOCUMENT_LINE_ID DOCUMENT_LINE_ID ,BC.EXP_ITEM_ID EXP_ITEM_ID ,BC.ORG_ID ORG_ID FROM PA_PROJECTS_ALL PR ,PA_TASKS TA ,PA_TASKS TOP_TA ,PA_BUDGET_VERSIONS GBV ,PA_BUDGET_TYPES PBT ,PA_LOOKUPS L1 ,PA_LOOKUPS L2 ,PA_LOOKUPS L3 ,PA_BC_PACKETS BC ,PA_EXPENDITURE_TYPES PET ,PA_BUDGET_ENTRY_METHODS PM ,PA_BUDGETARY_CONTROL_OPTIONS PB WHERE BC.PROJECT_ID = PR.PROJECT_ID AND TA.PROJECT_ID = PR.PROJECT_ID AND BC.TASK_ID = TA.TASK_ID AND BC.TOP_TASK_ID = TOP_TA.TASK_ID(+) AND BC.BUDGET_VERSION_ID = GBV.BUDGET_VERSION_ID AND GBV.budget_type_code = pbt.budget_type_code AND GBV.PROJECT_ID = PR.PROJECT_ID AND GBV.budget_entry_method_code = PM.budget_entry_method_code AND L1.LOOKUP_TYPE (+) = 'FC_STATUS_CODE' and L1.LOOKUP_CODE (+) = BC.STATUS_CODE and BC.STATUS_CODE <> 'X' and L2.LOOKUP_TYPE (+) = 'FC_DOC_TYPE' and L2.LOOKUP_CODE (+) = BC.DOCUMENT_TYPE and L3.LOOKUP_TYPE (+) = 'BUDGET STATUS' and L3.LOOKUP_CODE (+) = GBV.budget_status_code and BC.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE AND PB.BDGT_CNTRL_FLAG = 'Y' AND PB.project_id = BC.project_id AND PB.BUDGET_TYPE_CODE = GBV.BUDGET_TYPE_CODE AND ((bc.document_type in ('AP','PO','REQ','EXP','CC_P_PAY','CC_C_PAY') and NVL(pb.EXTERNAL_BUDGET_CODE,'GL') = 'GL') OR (bc.document_type in ('CC_P_CO','CC_C_CO') and pb.EXTERNAL_BUDGET_CODE = 'CC' ) ) UNION ALL SELECT BC.ROWID ROW_ID ,BC.PACKET_ID PACKET_ID ,BC.PROJECT_ID PROJECT_ID ,BC.TASK_ID TASK_ID ,BC.TOP_TASK_ID TOP_TASK_ID ,BC.EXPENDITURE_TYPE EXPENDITURE_TYPE ,PET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY ,BC.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE ,BC.GL_DATE GL_DATE ,BC.ACTUAL_FLAG ACTUAL_FLAG ,bc.status_code STATUS_CODE ,L1.MEANING STATUS_MEANING ,BC.JE_CATEGORY_NAME JE_CATEGORY_NAME ,BC.JE_SOURCE_NAME JE_SOURCE_NAME ,BC.DOCUMENT_TYPE DOC_TYPE_CODE ,substr(L2.MEANING,1,50) DOCUMENT_TYPE ,BC.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID ,BC.PERIOD_NAME PERIOD_NAME ,BC.PERIOD_YEAR PERIOD_YEAR ,BC.PERIOD_NUM PERIOD_NUM ,BC.DOCUMENT_HEADER_ID DOCUMENT_HEADER_ID ,BC.DOCUMENT_DISTRIBUTION_ID DOCUMENT_DISTRIBUTION_ID ,BC.BUDGET_VERSION_ID BUDGET_VERSION_ID ,NVL(BC.accounted_dr,0)-NVL(BC.accounted_cr,0) AMOUNT ,BC.EFFECT_ON_FUNDS_CODE EFFECT_ON_FUNDS_CODE ,BC.RESULT_CODE RESULT_CODE ,BC.BALANCE_POSTED_FLAG BALANCE_POSTED_FLAG ,PR.SEGMENT1 PROJECT ,TA.TASK_NUMBER TASK ,TOP_TA.TASK_NUMBER TOP_TASK ,NULL RESOURCE_NAME ,NULL RESOURCE_GROUP_NAME ,substr(pa_expenditures_utils.getorgtlname(BC.EXPENDITURE_ORGANIZATION_ID),1,30) EXP_ORG_NAME ,NULL VERSION_NAME ,TO_NUMBER(NULL) VERSION_NUMBER ,NULL BUDGET_STATUS_CODE ,NULL BUDGET_TYPE ,BC.BC_PACKET_ID BC_PACKET_ID ,BC.res_result_code RES_RESULT_CODE ,BC.res_grp_result_code RES_GRP_RESULT_CODE ,BC.task_result_code TASK_RESULT_CODE ,BC.top_task_result_code TOP_TASK_RESULT_CODE ,BC.project_result_code PROJECT_RESULT_CODE ,BC.project_acct_result_code PROJECT_ACCT_RESULT_CODE ,NVL(BC.parent_resource_id,0) PARENT_RESOURCE_ID ,BC.resource_list_member_id RESOURCE_LIST_MEMBER_ID ,BC.fc_start_date START_DATE ,BC.fc_end_date END_DATE ,0 RES_ENC_PENDING ,0 RES_GRP_ENC_PENDING ,0 TASK_ENC_PENDING ,0 TOP_TASK_ENC_PENDING ,0 PROJECT_ENC_PENDING ,0 PROJECT_ACCT_ENC_PENDING ,BC.BUDGET_CCID BUDGET_CCID ,BC.DOCUMENT_LINE_ID DOCUMENT_LINE_ID ,BC.EXP_ITEM_ID EXP_ITEM_ID ,BC.ORG_ID ORG_ID FROM PA_PROJECTS_ALL PR ,PA_TASKS TA ,PA_TASKS TOP_TA ,PA_LOOKUPS L1 ,PA_LOOKUPS L2 ,PA_BC_PACKETS BC ,PA_EXPENDITURE_TYPES PET WHERE pr.project_id = bc.project_id AND ta.project_id = pr.project_id AND bc.task_id = ta.task_id AND bc.top_task_id = top_ta.task_id (+) AND bc.budget_version_id = -9999 AND L1.LOOKUP_TYPE (+) = 'FC_STATUS_CODE' and L1.LOOKUP_CODE (+) = BC.STATUS_CODE and L2.LOOKUP_TYPE (+) = 'FC_DOC_TYPE' and L2.LOOKUP_CODE (+) = BC.DOCUMENT_TYPE and BC.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE ORDER BY PACKET_ID ,PROJECT_ID ,BUDGET_VERSION_ID ,START_DATE ,EFFECT_ON_FUNDS_CODE DESC ,TOP_TASK_ID ,TASK_ID ,PARENT_RESOURCE_ID ,RESOURCE_LIST_MEMBER_ID ,AMOUNT

View Text - HTML Formatted

SELECT BC.ROWID ROW_ID
, BC.PACKET_ID PACKET_ID
, BC.PROJECT_ID PROJECT_ID
, BC.TASK_ID TASK_ID
, BC.TOP_TASK_ID TOP_TASK_ID
, BC.EXPENDITURE_TYPE EXPENDITURE_TYPE
, PET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, BC.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, BC.GL_DATE GL_DATE
, BC.ACTUAL_FLAG ACTUAL_FLAG
, BC.STATUS_CODE STATUS_CODE
, L1.MEANING STATUS_MEANING
, BC.JE_CATEGORY_NAME JE_CATEGORY_NAME
, BC.JE_SOURCE_NAME JE_SOURCE_NAME
, BC.DOCUMENT_TYPE DOC_TYPE_CODE
, SUBSTR(L2.MEANING
, 1
, 50) DOCUMENT_TYPE
, BC.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, BC.PERIOD_NAME PERIOD_NAME
, BC.PERIOD_YEAR PERIOD_YEAR
, BC.PERIOD_NUM PERIOD_NUM
, BC.DOCUMENT_HEADER_ID DOCUMENT_HEADER_ID
, BC.DOCUMENT_DISTRIBUTION_ID DOCUMENT_DISTRIBUTION_ID
, BC.BUDGET_VERSION_ID BUDGET_VERSION_ID
, NVL(BC.ACCOUNTED_DR
, 0)-NVL(BC.ACCOUNTED_CR
, 0) AMOUNT
, BC.EFFECT_ON_FUNDS_CODE EFFECT_ON_FUNDS_CODE
, BC.RESULT_CODE RESULT_CODE
, BC.BALANCE_POSTED_FLAG BALANCE_POSTED_FLAG
, PR.SEGMENT1 PROJECT
, TA.TASK_NUMBER TASK
, TOP_TA.TASK_NUMBER TOP_TASK
, (SELECT RESG.NAME
FROM PA_RESOURCES RESG
, PA_RESOURCE_LIST_MEMBERS RLMI
WHERE RLMI.RESOURCE_LIST_MEMBER_ID = BC.RESOURCE_LIST_MEMBER_ID
AND RLMI.RESOURCE_ID = RESG.RESOURCE_ID) RESOURCE_NAME
, (SELECT RESG.NAME
FROM PA_RESOURCES RESG
, PA_RESOURCE_LIST_MEMBERS RLMI
WHERE RLMI.RESOURCE_LIST_MEMBER_ID = NVL(BC.PARENT_RESOURCE_ID
, BC.RESOURCE_LIST_MEMBER_ID)
AND RLMI.PARENT_MEMBER_ID IS NULL
AND RLMI.RESOURCE_ID = RESG.RESOURCE_ID) RESOURCE_GROUP_NAME
, SUBSTR(PA_EXPENDITURES_UTILS.GETORGTLNAME(BC.EXPENDITURE_ORGANIZATION_ID)
, 1
, 30) EXP_ORG_NAME
, GBV.VERSION_NAME VERSION_NAME
, GBV.VERSION_NUMBER VERSION_NUMBER
, L3.MEANING BUDGET_STATUS_CODE
, PBT.BUDGET_TYPE BUDGET_TYPE
, BC.BC_PACKET_ID BC_PACKET_ID
, BC.RES_RESULT_CODE RES_RESULT_CODE
, BC.RES_GRP_RESULT_CODE RES_GRP_RESULT_CODE
, BC.TASK_RESULT_CODE TASK_RESULT_CODE
, BC.TOP_TASK_RESULT_CODE TOP_TASK_RESULT_CODE
, BC.PROJECT_RESULT_CODE PROJECT_RESULT_CODE
, BC.PROJECT_ACCT_RESULT_CODE PROJECT_ACCT_RESULT_CODE
, NVL(BC.PARENT_RESOURCE_ID
, 0) PARENT_RESOURCE_ID
, BC.RESOURCE_LIST_MEMBER_ID RESOURCE_LIST_MEMBER_ID
, BC.FC_START_DATE START_DATE
, BC.FC_END_DATE END_DATE
, 0 RES_ENC_PENDING
, 0 RES_GRP_ENC_PENDING
, 0 TASK_ENC_PENDING
, 0 TOP_TASK_ENC_PENDING
, 0 PROJECT_ENC_PENDING
, 0 PROJECT_ACCT_ENC_PENDING
, BC.BUDGET_CCID BUDGET_CCID
, BC.DOCUMENT_LINE_ID DOCUMENT_LINE_ID
, BC.EXP_ITEM_ID EXP_ITEM_ID
, BC.ORG_ID ORG_ID
FROM PA_PROJECTS_ALL PR
, PA_TASKS TA
, PA_TASKS TOP_TA
, PA_BUDGET_VERSIONS GBV
, PA_BUDGET_TYPES PBT
, PA_LOOKUPS L1
, PA_LOOKUPS L2
, PA_LOOKUPS L3
, PA_BC_PACKETS BC
, PA_EXPENDITURE_TYPES PET
, PA_BUDGET_ENTRY_METHODS PM
, PA_BUDGETARY_CONTROL_OPTIONS PB
WHERE BC.PROJECT_ID = PR.PROJECT_ID
AND TA.PROJECT_ID = PR.PROJECT_ID
AND BC.TASK_ID = TA.TASK_ID
AND BC.TOP_TASK_ID = TOP_TA.TASK_ID(+)
AND BC.BUDGET_VERSION_ID = GBV.BUDGET_VERSION_ID
AND GBV.BUDGET_TYPE_CODE = PBT.BUDGET_TYPE_CODE
AND GBV.PROJECT_ID = PR.PROJECT_ID
AND GBV.BUDGET_ENTRY_METHOD_CODE = PM.BUDGET_ENTRY_METHOD_CODE
AND L1.LOOKUP_TYPE (+) = 'FC_STATUS_CODE'
AND L1.LOOKUP_CODE (+) = BC.STATUS_CODE
AND BC.STATUS_CODE <> 'X'
AND L2.LOOKUP_TYPE (+) = 'FC_DOC_TYPE'
AND L2.LOOKUP_CODE (+) = BC.DOCUMENT_TYPE
AND L3.LOOKUP_TYPE (+) = 'BUDGET STATUS'
AND L3.LOOKUP_CODE (+) = GBV.BUDGET_STATUS_CODE
AND BC.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE
AND PB.BDGT_CNTRL_FLAG = 'Y'
AND PB.PROJECT_ID = BC.PROJECT_ID
AND PB.BUDGET_TYPE_CODE = GBV.BUDGET_TYPE_CODE
AND ((BC.DOCUMENT_TYPE IN ('AP'
, 'PO'
, 'REQ'
, 'EXP'
, 'CC_P_PAY'
, 'CC_C_PAY')
AND NVL(PB.EXTERNAL_BUDGET_CODE
, 'GL') = 'GL') OR (BC.DOCUMENT_TYPE IN ('CC_P_CO'
, 'CC_C_CO')
AND PB.EXTERNAL_BUDGET_CODE = 'CC' ) ) UNION ALL SELECT BC.ROWID ROW_ID
, BC.PACKET_ID PACKET_ID
, BC.PROJECT_ID PROJECT_ID
, BC.TASK_ID TASK_ID
, BC.TOP_TASK_ID TOP_TASK_ID
, BC.EXPENDITURE_TYPE EXPENDITURE_TYPE
, PET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, BC.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, BC.GL_DATE GL_DATE
, BC.ACTUAL_FLAG ACTUAL_FLAG
, BC.STATUS_CODE STATUS_CODE
, L1.MEANING STATUS_MEANING
, BC.JE_CATEGORY_NAME JE_CATEGORY_NAME
, BC.JE_SOURCE_NAME JE_SOURCE_NAME
, BC.DOCUMENT_TYPE DOC_TYPE_CODE
, SUBSTR(L2.MEANING
, 1
, 50) DOCUMENT_TYPE
, BC.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, BC.PERIOD_NAME PERIOD_NAME
, BC.PERIOD_YEAR PERIOD_YEAR
, BC.PERIOD_NUM PERIOD_NUM
, BC.DOCUMENT_HEADER_ID DOCUMENT_HEADER_ID
, BC.DOCUMENT_DISTRIBUTION_ID DOCUMENT_DISTRIBUTION_ID
, BC.BUDGET_VERSION_ID BUDGET_VERSION_ID
, NVL(BC.ACCOUNTED_DR
, 0)-NVL(BC.ACCOUNTED_CR
, 0) AMOUNT
, BC.EFFECT_ON_FUNDS_CODE EFFECT_ON_FUNDS_CODE
, BC.RESULT_CODE RESULT_CODE
, BC.BALANCE_POSTED_FLAG BALANCE_POSTED_FLAG
, PR.SEGMENT1 PROJECT
, TA.TASK_NUMBER TASK
, TOP_TA.TASK_NUMBER TOP_TASK
, NULL RESOURCE_NAME
, NULL RESOURCE_GROUP_NAME
, SUBSTR(PA_EXPENDITURES_UTILS.GETORGTLNAME(BC.EXPENDITURE_ORGANIZATION_ID)
, 1
, 30) EXP_ORG_NAME
, NULL VERSION_NAME
, TO_NUMBER(NULL) VERSION_NUMBER
, NULL BUDGET_STATUS_CODE
, NULL BUDGET_TYPE
, BC.BC_PACKET_ID BC_PACKET_ID
, BC.RES_RESULT_CODE RES_RESULT_CODE
, BC.RES_GRP_RESULT_CODE RES_GRP_RESULT_CODE
, BC.TASK_RESULT_CODE TASK_RESULT_CODE
, BC.TOP_TASK_RESULT_CODE TOP_TASK_RESULT_CODE
, BC.PROJECT_RESULT_CODE PROJECT_RESULT_CODE
, BC.PROJECT_ACCT_RESULT_CODE PROJECT_ACCT_RESULT_CODE
, NVL(BC.PARENT_RESOURCE_ID
, 0) PARENT_RESOURCE_ID
, BC.RESOURCE_LIST_MEMBER_ID RESOURCE_LIST_MEMBER_ID
, BC.FC_START_DATE START_DATE
, BC.FC_END_DATE END_DATE
, 0 RES_ENC_PENDING
, 0 RES_GRP_ENC_PENDING
, 0 TASK_ENC_PENDING
, 0 TOP_TASK_ENC_PENDING
, 0 PROJECT_ENC_PENDING
, 0 PROJECT_ACCT_ENC_PENDING
, BC.BUDGET_CCID BUDGET_CCID
, BC.DOCUMENT_LINE_ID DOCUMENT_LINE_ID
, BC.EXP_ITEM_ID EXP_ITEM_ID
, BC.ORG_ID ORG_ID
FROM PA_PROJECTS_ALL PR
, PA_TASKS TA
, PA_TASKS TOP_TA
, PA_LOOKUPS L1
, PA_LOOKUPS L2
, PA_BC_PACKETS BC
, PA_EXPENDITURE_TYPES PET
WHERE PR.PROJECT_ID = BC.PROJECT_ID
AND TA.PROJECT_ID = PR.PROJECT_ID
AND BC.TASK_ID = TA.TASK_ID
AND BC.TOP_TASK_ID = TOP_TA.TASK_ID (+)
AND BC.BUDGET_VERSION_ID = -9999
AND L1.LOOKUP_TYPE (+) = 'FC_STATUS_CODE'
AND L1.LOOKUP_CODE (+) = BC.STATUS_CODE
AND L2.LOOKUP_TYPE (+) = 'FC_DOC_TYPE'
AND L2.LOOKUP_CODE (+) = BC.DOCUMENT_TYPE
AND BC.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE ORDER BY PACKET_ID
, PROJECT_ID
, BUDGET_VERSION_ID
, START_DATE
, EFFECT_ON_FUNDS_CODE DESC
, TOP_TASK_ID
, TASK_ID
, PARENT_RESOURCE_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT