PJB_XLA_REV_V

Details

• Schema: FUSION

• Object owner: PJB

• Object type: VIEW

Columns

Name

CONTRACT_ID CONTRACT_NAME CONTRACT_NUMBER MAJOR_VERSION CONTRACT_TYPE_ID CONTRACT_TYPE CONTRACT_ORGANIZATION_NAME CONTRACT_ORGANIZATION_ID CONTRACT_LINE_ID CONTRACT_LINE REV_DISTRIBUTION_ID BILL_TRX_ID BILL_TRX_MANUAL_FLAG CONTRACT_PROJECT_LINKAGE_ID REVENUE_PLAN_ID REVENUE_METHOD_ID CUSTOMER_ID CUSTOMER_NAME LINKED_PROJECT_ID LINKED_PROJECT_NAME LINKED_PROJECT_NUMBER LINKED_TASK_ID LINKED_TASK_NAME LINKED_TASK_NUMBER TRANSACTION_TYPE_CODE EXPENDITURE_ITEM_ID BILLING_EVENT_ID EXPENDITURE_TYPE_ID EXPENDITURE_TYPE BILLING_EVENT_TYPE_ID BILLING_EVENT_TYPE SYSTEM_LINKAGE_FUNCTION EXPENDITURE_CATEGORY_ID EXPENDITURE_CATEGORY TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_NAME TRANSACTION_PROJECT_NUMBER TRANSACTION_TASK_ID TRANSACTION_TASK_NAME TRANSACTION_TASK_NUMBER RBS_ELEMENT_ID REV_LINE_NUMBER REVENUE_CATEGORY BILLING_TYPE_CODE TRNS_CURRENCY_CODE TRNS_CURR_REVENUE_AMT REVENUE_CURR_AMT REVENUE_CURRENCY_CODE LEDGER_CURR_REVENUE_AMT LEDGER_CURRENCY_CODE PROJECT_CURRENCY_CODE PROJECT_CURR_REVENUE_AMT CONTRACT_CURRENCY_CODE CONT_CURR_REVENUE_AMT REVERSED_REV_DISTRIBUTION_ID REVERSED_FLAG REV_LINE_NUM_REVERSED ACCOUNTING_DATE PROJECT_ACCOUNTING_DATE

Query

SQL_Statement

SELECT ContHead.ID CONTRACT_ID ,ContHeadTL.COGNOMEN CONTRACT_NAME ,ContHead.CONTRACT_NUMBER CONTRACT_NUMBER ,ContHead.MAJOR_VERSION MAJOR_VERSION ,ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID ,ContTypeTL.NAME CONTRACT_TYPE ,OrgUnit.NAME CONTRACT_ORGANIZATION_NAME ,ContHead.ORG_ID CONTRACT_ORGANIZATION_ID ,RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID ,ContLine.LINE_NUMBER CONTRACT_LINE ,RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID ,RevDist.BILL_TRX_ID BILL_TRX_ID ,BillTrx.MANUAL_FLAG BILL_TRX_MANUAL_FLAG ,RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID ,RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID ,RevPlan.BILL_METHOD_ID REVENUE_METHOD_ID ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID ,Party.PARTY_NAME CUSTOMER_NAME ,RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID ,LinkProj.PROJECT_ID LINKED_PROJECT_NAME ,LinkProj.SEGMENT1 LINKED_PROJECT_NUMBER ,RevDist.LINKED_TASK_ID LINKED_TASK_ID ,LinkTask.PROJ_ELEMENT_ID LINKED_TASK_NAME ,LinkTask.ELEMENT_NUMBER LINKED_TASK_NUMBER ,RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_ID,NULL) EXPENDITURE_ITEM_ID ,DECODE(RevDist.Bill_transaction_type_Code,'EVT',RevDist.TRANSACTION_ID,NULL) BILLING_EVENT_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE , revdist.system_linkage_function /* 30623237 (SELECT exp.system_linkage_function FROM pjc_Exp_items_all exp WHERE exp.expenditure_item_id = RevDist.TRANSACTION_ID AND RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' )*/ SYSTEM_LINKAGE_FUNCTION ,(SELECT expTypes.expenditure_category_id FROM pjf_Exp_types_b exptypes WHERE RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' AND exptypes.EXPENDITURE_TYPE_ID = RevDist.transaction_type_id) EXPENDITURE_CATEGORY_ID ,(SELECT expTypes.expenditure_category_id FROM pjf_Exp_types_b exptypes WHERE RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' AND exptypes.EXPENDITURE_TYPE_ID = RevDist.transaction_type_id) EXPENDITURE_CATEGORY ,RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID ,TransProj.PROJECT_ID TRANSACTION_PROJECT_NAME ,TransProj.SEGMENT1 TRANSACTION_PROJECT_NUMBER ,RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_ID ,TransTask.PROJ_ELEMENT_ID TRANSACTION_TASK_NAME ,TransTask.ELEMENT_NUMBER TRANSACTION_TASK_NUMBER ,RevDist.RBS_ELEMENT_ID RBS_ELEMENT_ID ,RevDist.LINE_NUM REV_LINE_NUMBER ,RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY ,RevDist.BILLING_TYPE_CODE BILLING_TYPE_CODE ,RevDist.TRNS_CURRENCY_CODE TRNS_CURRENCY_CODE ,RevDist.TRNS_CURR_REVENUE_AMT TRNS_CURR_REVENUE_AMT ,RevDist.REVENUE_CURR_AMT REVENUE_CURR_AMT ,RevDist.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE ,RevDist.LEDGER_CURR_REVENUE_AMT LEDGER_CURR_REVENUE_AMT ,RevDist.LEDGER_CURRENCY_CODE LEDGER_CURRENCY_CODE ,RevDist.PROJECT_CURRENCY_CODE PROJECT_CURRENCY_CODE ,RevDist.PROJECT_CURR_REVENUE_AMT PROJECT_CURR_REVENUE_AMT ,RevDist.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE ,RevDist.CONT_CURR_REVENUE_AMT CONT_CURR_REVENUE_AMT ,RevDist.REVERSED_REV_DISTRIBUTION_ID REVERSED_REV_DISTRIBUTION_ID ,RevDist.REVERSED_FLAG REVERSED_FLAG ,RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED ,RevDist.GL_DATE ACCOUNTING_DATE ,RevDist.PA_DATE PROJECT_ACCOUNTING_DATE FROM PJB_REV_DISTRIBUTIONS RevDist ,OKC_K_HEADERS_ALL_B ContHead ,OKC_K_HEADERS_TL ContHeadTL ,OKC_CONTRACT_TYPES_B ContType ,OKC_CONTRACT_TYPES_TL ContTypeTL ,OKC_K_LINES_B ContLine ,HR_ORGANIZATION_UNITS_F_TL OrgUnit ,PJB_BILL_TRXS BillTrx ,PJB_BILL_PLANS_B RevPlan ,PJB_BILL_PLANS_B BillPlan ,PJF_PROJECTS_ALL_B LinkProj ,PJF_PROJ_ELEMENTS_B LinkTask ,PJF_PROJECTS_ALL_B TransProj ,PJF_PROJ_ELEMENTS_B TransTask ,HZ_CUST_ACCOUNTS CustAcct ,HZ_PARTIES Party WHERE RevDist.CONTRACT_ID = ContHead.ID AND ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContHead.Id = ContHeadTL.Id AND ContHead.major_version = ContHeadTL.major_version AND ContHeadTL.Language = USERENV('LANG') AND RevDist.CONTRACT_LINE_ID = ContLine.ID AND ContLine.VERSION_TYPE IN ('A','C') AND ContLine.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContHead.ORG_ID = OrgUnit.ORGANIZATION_ID AND OrgUnit.Language = USERENV('LANG') AND TRUNC(sysdate) BETWEEN OrgUnit.Effective_Start_Date AND OrgUnit.Effective_End_Date AND ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND ContType.contract_type_id = ContTypeTL.contract_type_id AND ContTypeTL.Language = USERENV('LANG') AND ContHead.TEMPLATE_YN = 'N' AND RevDist.REVENUE_PLAN_ID = RevPlan.BILL_PLAN_ID AND RevPlan.MAJOR_VERSION = ContLine.MAJOR_VERSION AND BillTrx.BILL_TRX_ID = RevDist.BILL_TRX_ID AND BillTrx.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID AND BillPlan.MAJOR_VERSION = ContLine.MAJOR_VERSION AND RevDist.LINKED_PROJECT_ID = LinkProj.PROJECT_ID(+) AND RevDist.LINKED_TASK_ID = LinkTask.PROJ_ELEMENT_ID (+) AND RevDist.TRANSACTION_PROJECT_ID = TransProj.PROJECT_ID(+) AND RevDist.TRANSACTION_TASK_ID = TransTask.PROJ_ELEMENT_ID(+) AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID AND BillTrx.Contract_id = ContHead.Id AND BillTrx.Contract_Line_id = ContLine.id AND CustAcct.PARTY_ID = Party.PARTY_ID AND ContHead.id = ContLine.dnz_chr_id AND ContHead.major_version = ContLine.major_version UNION ALL SELECT ContHead.ID CONTRACT_ID ,ContHeadTL.COGNOMEN CONTRACT_NAME ,ContHead.CONTRACT_NUMBER CONTRACT_NUMBER ,ContHead.MAJOR_VERSION MAJOR_VERSION ,ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID ,ContTypeTL.NAME CONTRACT_TYPE ,OrgUnit.NAME CONTRACT_ORGANIZATION_NAME ,ContHead.ORG_ID CONTRACT_ORGANIZATION_ID ,RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID ,ContLine.LINE_NUMBER CONTRACT_LINE ,RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID ,RevDist.BILL_TRX_ID BILL_TRX_ID ,NULL BILL_TRX_MANUAL_FLAG ,RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID ,RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID ,RevPlan.BILL_METHOD_ID REVENUE_METHOD_ID ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID ,Party.PARTY_NAME CUSTOMER_NAME ,RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID ,LinkProj.PROJECT_ID LINKED_PROJECT_NAME ,LinkProj.SEGMENT1 LINKED_PROJECT_NUMBER ,RevDist.LINKED_TASK_ID LINKED_TASK_ID ,LinkTask.PROJ_ELEMENT_ID LINKED_TASK_NAME ,LinkTask.ELEMENT_NUMBER LINKED_TASK_NUMBER ,RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_ID,NULL) EXPENDITURE_ITEM_ID ,DECODE(RevDist.Bill_transaction_type_Code,'EVT',RevDist.TRANSACTION_ID,NULL) BILLING_EVENT_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE_ID ,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE,'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE , revdist.system_linkage_function SYSTEM_LINKAGE_FUNCTION ,(SELECT expTypes.expenditure_category_id FROM pjf_Exp_types_b exptypes WHERE RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' AND exptypes.EXPENDITURE_TYPE_ID = RevDist.transaction_type_id) EXPENDITURE_CATEGORY_ID ,(SELECT expTypes.expenditure_category_id FROM pjf_Exp_types_b exptypes WHERE RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' AND exptypes.EXPENDITURE_TYPE_ID = RevDist.transaction_type_id) EXPENDITURE_CATEGORY ,RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID ,TransProj.PROJECT_ID TRANSACTION_PROJECT_NAME ,TransProj.SEGMENT1 TRANSACTION_PROJECT_NUMBER ,RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_ID ,TransTask.PROJ_ELEMENT_ID TRANSACTION_TASK_NAME ,TransTask.ELEMENT_NUMBER TRANSACTION_TASK_NUMBER ,RevDist.RBS_ELEMENT_ID RBS_ELEMENT_ID ,RevDist.LINE_NUM REV_LINE_NUMBER ,RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY ,RevDist.BILLING_TYPE_CODE BILLING_TYPE_CODE ,RevDist.TRNS_CURRENCY_CODE TRNS_CURRENCY_CODE ,RevDist.TRNS_CURR_REVENUE_AMT TRNS_CURR_REVENUE_AMT ,RevDist.REVENUE_CURR_AMT REVENUE_CURR_AMT ,RevDist.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE ,RevDist.LEDGER_CURR_REVENUE_AMT LEDGER_CURR_REVENUE_AMT ,RevDist.LEDGER_CURRENCY_CODE LEDGER_CURRENCY_CODE ,RevDist.PROJECT_CURRENCY_CODE PROJECT_CURRENCY_CODE ,RevDist.PROJECT_CURR_REVENUE_AMT PROJECT_CURR_REVENUE_AMT ,RevDist.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE ,RevDist.CONT_CURR_REVENUE_AMT CONT_CURR_REVENUE_AMT ,RevDist.REVERSED_REV_DISTRIBUTION_ID REVERSED_REV_DISTRIBUTION_ID ,RevDist.REVERSED_FLAG REVERSED_FLAG ,RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED ,RevDist.GL_DATE ACCOUNTING_DATE ,RevDist.PA_DATE PROJECT_ACCOUNTING_DATE FROM PJB_REV_DISTRIBUTIONS RevDist ,OKC_K_HEADERS_ALL_B ContHead ,OKC_K_HEADERS_TL ContHeadTL ,OKC_CONTRACT_TYPES_B ContType ,OKC_CONTRACT_TYPES_TL ContTypeTL ,OKC_K_LINES_B ContLine ,HR_ORGANIZATION_UNITS_F_TL OrgUnit ,PJB_BILL_PLANS_B RevPlan ,PJB_BILL_PLANS_B BillPlan ,PJF_PROJECTS_ALL_B LinkProj ,PJF_PROJ_ELEMENTS_B LinkTask ,PJF_PROJECTS_ALL_B TransProj ,PJF_PROJ_ELEMENTS_B TransTask ,HZ_CUST_ACCOUNTS CustAcct ,HZ_PARTIES Party ,OKC_BU_IMPL_OPTIONS_ALL BU WHERE RevDist.CONTRACT_ID = ContHead.ID AND ContHead.VERSION_TYPE ='C' AND ContHead.STS_CODE = 'UNDER_AMENDMENT' AND ContHead.under_amend_version_flag ='N' AND ContHead.Org_id = BU.org_id AND BU.attribute15 ='INCLUDE_UNDER_AMENDMENT' AND ContHead.Id = ContHeadTL.Id AND ContHead.major_version = ContHeadTL.major_version AND ContHeadTL.Language = USERENV('LANG') AND RevDist.CONTRACT_LINE_ID = ContLine.ID AND ContLine.VERSION_TYPE ='C' AND ContLine.STS_CODE IN ( 'UNDER_AMENDMENT','CLOSED') AND ContHead.ORG_ID = OrgUnit.ORGANIZATION_ID AND OrgUnit.Language = USERENV('LANG') AND TRUNC(sysdate) BETWEEN OrgUnit.Effective_Start_Date AND OrgUnit.Effective_End_Date AND ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND ContType.contract_type_id = ContTypeTL.contract_type_id AND ContTypeTL.Language = USERENV('LANG') AND ContHead.TEMPLATE_YN = 'N' AND RevDist.REVENUE_PLAN_ID = RevPlan.BILL_PLAN_ID AND RevPlan.MAJOR_VERSION = ContLine.MAJOR_VERSION AND ContLine.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID AND BillPlan.MAJOR_VERSION = ContLine.MAJOR_VERSION AND RevDist.LINKED_PROJECT_ID = LinkProj.PROJECT_ID(+) AND RevDist.LINKED_TASK_ID = LinkTask.PROJ_ELEMENT_ID (+) AND RevDist.TRANSACTION_PROJECT_ID = TransProj.PROJECT_ID(+) AND RevDist.TRANSACTION_TASK_ID = TransTask.PROJ_ELEMENT_ID(+) AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID AND CustAcct.PARTY_ID = Party.PARTY_ID AND ContHead.id = ContLine.dnz_chr_id AND ContHead.major_version = ContLine.major_version