VIEW: APPS.OZF_XLA_ACC_HEADER_V

Source

View Text - Preformatted

SELECT /*+ leading(XEG) use_nl(XEG oxa )*/ XEG.EVENT_ID EVENT_ID, 'C' THIRD_PARTY_TYPE, UTIL.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, UTIL.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID, UTIL.PLAN_CURR_AMOUNT ACCRUAL_AMOUNT, UTIL.PLAN_CURRENCY_CODE ACCRUAL_CURRENCY, UTIL.ACCTD_AMOUNT ACCRUAL_LEDGER_AMOUNT, (UTIL.PLAN_CURR_AMOUNT_REMAINING)*-1 PAID_ADJ_AMOUNT, (UTIL.ACCTD_AMOUNT_REMAINING)*-1 PAID_ADJ_LEDGER_AMOUNT, 'ACCRUAL' DISTRIBUTION_TYPE, UTIL.UTILIZATION_ID DISTRIBUTION_ID, UTIL.PRODUCT_ID PRODUCT_ID, DECODE(UTIL.PRODUCT_LEVEL_TYPE, 'PRODUCT', FND_PROFILE.VALUE('QP_ORGANIZATION_ID') , NULL) INVENTORY_ORGANIZATION_ID, UTIL.PLAN_TYPE ACTIVITY_TYPE, QPH.NAME ACTIVITY_CODE, FUND.SHORT_NAME BUDGET_NAME, FUND.FUND_NUMBER BUDGET_NUMBER, UTIL.UTILIZATION_TYPE UTILIZATION_TYPE, UTIL.ADJUSTMENT_TYPE ADJUSTMENT_TYPE, UTIL.ADJUSTMENT_TYPE_ID ADJUSTMENT_TYPE_ID, UTIL.EXCHANGE_RATE EXCHANGE_RATE, UTIL.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE, UTIL.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE, UTIL.GL_DATE GL_DATE, UTIL.OBJECT_TYPE OBJECT_TYPE, UTIL.OBJECT_ID OBJECT_ID, UTIL.ORDER_LINE_ID ORDER_LINE_ID, UTIL.PRICE_ADJUSTMENT_ID PRICE_ADJUSTMENT_ID, UTIL.REFERENCE_TYPE REFERENCE_TYPE, UTIL.REFERENCE_ID REFERENCE_ID, SYSPARAM.GL_ID_DED_ADJ SYS_PARAM_EXPENSE_ACCOUNT_CCID, SYSPARAM.GL_ID_ACCR_PROMO_LIAB SYS_PARAM_LIABILITY_ACCT_CCID, CATG.DED_ADJUSTMENT_ACCOUNT FUND_CATG_EXPENSE_ACCT_CCID, CATG.ACCRUED_LIABILITY_ACCOUNT FUND_CATG_LIABILITY_ACCT_CCID, FUND.DED_ADJUSTMENT_ACCOUNT FUND_EXPENSE_ACCT_CCID, FUND.ACCRUED_LIABLE_ACCOUNT FUND_LIABILITY_ACCT_CCID, CTYPE.GL_ID_DED_ADJ ADJ_EXPENSE_ACCT_CCID, CTYPE.GL_ID_ACCR_PROMO_LIAB ADJ_LIABILITY_ACCT_CCID, OXA.ADJUSTMENT_CR_ACCT_CCID ADJUSTMENT_CR_ACCT_CCID, OXA.ADJUSTMENT_DR_ACCT_CCID ADJUSTMENT_DR_ACCT_CCID, OZF_UTILITY_PVT.GET_REVENUE_ACCOUNT(UTIL.UTILIZATION_ID, UTIL.ORG_ID) REVENUE_ACCT_CCID, 'Y' ACCOUNTED_AMOUNT_OVERRIDE_FLAG, OXA.ACTG_ACCRUAL_ACCT_CCID ACTG_ACCRUAL_ACCT_CCID, OXA.ACTG_EXPENSE_ACCT_CCID ACTG_EXPENSE_ACCT_CCID, UTIL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, UTIL.ATTRIBUTE1 ATTRIBUTE1, UTIL.ATTRIBUTE2 ATTRIBUTE2, UTIL.ATTRIBUTE3 ATTRIBUTE3, UTIL.ATTRIBUTE4 ATTRIBUTE4, UTIL.ATTRIBUTE5 ATTRIBUTE5, UTIL.ATTRIBUTE6 ATTRIBUTE6, UTIL.ATTRIBUTE7 ATTRIBUTE7, UTIL.ATTRIBUTE8 ATTRIBUTE8, UTIL.ATTRIBUTE9 ATTRIBUTE9, UTIL.ATTRIBUTE10 ATTRIBUTE10, UTIL.ATTRIBUTE11 ATTRIBUTE11, UTIL.ATTRIBUTE12 ATTRIBUTE12, UTIL.ATTRIBUTE13 ATTRIBUTE13, UTIL.ATTRIBUTE14 ATTRIBUTE14, UTIL.ATTRIBUTE15 ATTRIBUTE15 FROM OZF_FUNDS_UTILIZED_ALL_B UTIL, OZF_XLA_ACCRUALS OXA, QP_LIST_HEADERS_ALL QPH, OZF_FUNDS_ALL_VL FUND, OZF_SYS_PARAMETERS_ALL SYSPARAM, AMS_CATEGORIES_B CATG, OZF_CLAIM_TYPES_ALL_B CTYPE, XLA_EVENTS_GT XEG WHERE UTIL.UTILIZATION_ID = OXA.UTILIZATION_ID AND UTIL.PLAN_ID = QPH.LIST_HEADER_ID AND XEG.ENTITY_CODE = 'ACCRUAL' AND XEG.SOURCE_ID_INT_1 = OXA.UTILIZATION_ID AND OXA.EVENT_TYPE_CODE = XEG.EVENT_TYPE_CODE AND XEG.APPLICATION_ID = 682 AND UTIL.FUND_ID = FUND.FUND_ID AND UTIL.ORG_ID = SYSPARAM.ORG_ID AND FUND.CATEGORY_ID = CATG.CATEGORY_ID AND UTIL.ADJUSTMENT_TYPE_ID = CTYPE.CLAIM_TYPE_ID(+) AND UTIL.ORG_ID = CTYPE.ORG_ID(+)

View Text - HTML Formatted

SELECT /*+ LEADING(XEG) USE_NL(XEG OXA )*/ XEG.EVENT_ID EVENT_ID
, 'C' THIRD_PARTY_TYPE
, UTIL.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, UTIL.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, UTIL.PLAN_CURR_AMOUNT ACCRUAL_AMOUNT
, UTIL.PLAN_CURRENCY_CODE ACCRUAL_CURRENCY
, UTIL.ACCTD_AMOUNT ACCRUAL_LEDGER_AMOUNT
, (UTIL.PLAN_CURR_AMOUNT_REMAINING)*-1 PAID_ADJ_AMOUNT
, (UTIL.ACCTD_AMOUNT_REMAINING)*-1 PAID_ADJ_LEDGER_AMOUNT
, 'ACCRUAL' DISTRIBUTION_TYPE
, UTIL.UTILIZATION_ID DISTRIBUTION_ID
, UTIL.PRODUCT_ID PRODUCT_ID
, DECODE(UTIL.PRODUCT_LEVEL_TYPE
, 'PRODUCT'
, FND_PROFILE.VALUE('QP_ORGANIZATION_ID')
, NULL) INVENTORY_ORGANIZATION_ID
, UTIL.PLAN_TYPE ACTIVITY_TYPE
, QPH.NAME ACTIVITY_CODE
, FUND.SHORT_NAME BUDGET_NAME
, FUND.FUND_NUMBER BUDGET_NUMBER
, UTIL.UTILIZATION_TYPE UTILIZATION_TYPE
, UTIL.ADJUSTMENT_TYPE ADJUSTMENT_TYPE
, UTIL.ADJUSTMENT_TYPE_ID ADJUSTMENT_TYPE_ID
, UTIL.EXCHANGE_RATE EXCHANGE_RATE
, UTIL.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, UTIL.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, UTIL.GL_DATE GL_DATE
, UTIL.OBJECT_TYPE OBJECT_TYPE
, UTIL.OBJECT_ID OBJECT_ID
, UTIL.ORDER_LINE_ID ORDER_LINE_ID
, UTIL.PRICE_ADJUSTMENT_ID PRICE_ADJUSTMENT_ID
, UTIL.REFERENCE_TYPE REFERENCE_TYPE
, UTIL.REFERENCE_ID REFERENCE_ID
, SYSPARAM.GL_ID_DED_ADJ SYS_PARAM_EXPENSE_ACCOUNT_CCID
, SYSPARAM.GL_ID_ACCR_PROMO_LIAB SYS_PARAM_LIABILITY_ACCT_CCID
, CATG.DED_ADJUSTMENT_ACCOUNT FUND_CATG_EXPENSE_ACCT_CCID
, CATG.ACCRUED_LIABILITY_ACCOUNT FUND_CATG_LIABILITY_ACCT_CCID
, FUND.DED_ADJUSTMENT_ACCOUNT FUND_EXPENSE_ACCT_CCID
, FUND.ACCRUED_LIABLE_ACCOUNT FUND_LIABILITY_ACCT_CCID
, CTYPE.GL_ID_DED_ADJ ADJ_EXPENSE_ACCT_CCID
, CTYPE.GL_ID_ACCR_PROMO_LIAB ADJ_LIABILITY_ACCT_CCID
, OXA.ADJUSTMENT_CR_ACCT_CCID ADJUSTMENT_CR_ACCT_CCID
, OXA.ADJUSTMENT_DR_ACCT_CCID ADJUSTMENT_DR_ACCT_CCID
, OZF_UTILITY_PVT.GET_REVENUE_ACCOUNT(UTIL.UTILIZATION_ID
, UTIL.ORG_ID) REVENUE_ACCT_CCID
, 'Y' ACCOUNTED_AMOUNT_OVERRIDE_FLAG
, OXA.ACTG_ACCRUAL_ACCT_CCID ACTG_ACCRUAL_ACCT_CCID
, OXA.ACTG_EXPENSE_ACCT_CCID ACTG_EXPENSE_ACCT_CCID
, UTIL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, UTIL.ATTRIBUTE1 ATTRIBUTE1
, UTIL.ATTRIBUTE2 ATTRIBUTE2
, UTIL.ATTRIBUTE3 ATTRIBUTE3
, UTIL.ATTRIBUTE4 ATTRIBUTE4
, UTIL.ATTRIBUTE5 ATTRIBUTE5
, UTIL.ATTRIBUTE6 ATTRIBUTE6
, UTIL.ATTRIBUTE7 ATTRIBUTE7
, UTIL.ATTRIBUTE8 ATTRIBUTE8
, UTIL.ATTRIBUTE9 ATTRIBUTE9
, UTIL.ATTRIBUTE10 ATTRIBUTE10
, UTIL.ATTRIBUTE11 ATTRIBUTE11
, UTIL.ATTRIBUTE12 ATTRIBUTE12
, UTIL.ATTRIBUTE13 ATTRIBUTE13
, UTIL.ATTRIBUTE14 ATTRIBUTE14
, UTIL.ATTRIBUTE15 ATTRIBUTE15
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, OZF_XLA_ACCRUALS OXA
, QP_LIST_HEADERS_ALL QPH
, OZF_FUNDS_ALL_VL FUND
, OZF_SYS_PARAMETERS_ALL SYSPARAM
, AMS_CATEGORIES_B CATG
, OZF_CLAIM_TYPES_ALL_B CTYPE
, XLA_EVENTS_GT XEG
WHERE UTIL.UTILIZATION_ID = OXA.UTILIZATION_ID
AND UTIL.PLAN_ID = QPH.LIST_HEADER_ID
AND XEG.ENTITY_CODE = 'ACCRUAL'
AND XEG.SOURCE_ID_INT_1 = OXA.UTILIZATION_ID
AND OXA.EVENT_TYPE_CODE = XEG.EVENT_TYPE_CODE
AND XEG.APPLICATION_ID = 682
AND UTIL.FUND_ID = FUND.FUND_ID
AND UTIL.ORG_ID = SYSPARAM.ORG_ID
AND FUND.CATEGORY_ID = CATG.CATEGORY_ID
AND UTIL.ADJUSTMENT_TYPE_ID = CTYPE.CLAIM_TYPE_ID(+)
AND UTIL.ORG_ID = CTYPE.ORG_ID(+)