EXM_XCC_AUTH_EXTRACT_V

Details

• Schema: FUSION

• Object owner: EXM

• Object type: VIEW

Columns

Name

DATA_SET_ID SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 TRANSACTION_NUMBER SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 LINE_GROUP_CODE LINE_NUM LEDGER_ID BUSINESS_UNIT_ID DESTINATION_TYPE_CODE DOCUMENT_TYPE_CODE TRANSACTION_SOURCE_CODE TRANSACTION_SUBTYPE_CODE JE_SOURCE_CODE JE_CATEGORY_CODE BUDGET_DATE ACCOUNTING_DATE ENCUMBRANCE_TYPE_CODE ENTERED_CURRENCY UOM_CODE QUANTITY PRICE ENTERED_AMOUNT CONVERSION_TYPE_CODE CONVERSION_DATE LEDGER_AMOUNT LIQUIDATION_DATE LIQUIDATION_QUANTITY LIQUIDATION_AMOUNT LIQUIDATION_TRANS_TYPE_CODE LIQUIDATION_LINE_ID_1 LIQUIDATION_LINE_ID_2 LIQUIDATION_LINE_ID_3 LIQUIDATION_LINE_ID_4 LIQUIDATION_LINE_ID_5 LIQUIDATION_LINE_ID_6 STATISTICAL_AMOUNT BUDGET_CCID VENDOR_ID INVENTORY_ITEM_ID ORDER_TYPE_INFO CODE_COMBINATION_ID PJC_PROJECT_ID PJC_TASK_ID PJC_RESOURCE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_WORK_TYPE_ID CONTROL_BUDGET_ID PJC_FUNDING_SOURCE

Query

SQL_Statement

SELECT SA.XCC_DATA_SET_ID DATA_SET_ID , TO_CHAR(SA.SPEND_AUTHORIZATION_ID) SOURCE_HEADER_ID_1 , ' ' SOURCE_HEADER_ID_2 , SA.SPEND_AUTHORIZATION_NUM TRANSACTION_NUMBER , TO_CHAR(SAL.SPEND_AUTH_LINE_ID) SOURCE_LINE_ID_1 , TO_CHAR(SAD.SPEND_AUTH_DIST_ID) SOURCE_LINE_ID_2 , (case when SA.CREATION_METHOD_CODE ='CLOSED' THEN TO_CHAR(SA.XCC_DATA_SET_ID) else ' ' end ) SOURCE_LINE_ID_3 , ' ' SOURCE_LINE_ID_4 , ' ' SOURCE_LINE_ID_5 , ' ' SOURCE_LINE_ID_6 , NULL LINE_GROUP_CODE , NULL LINE_NUM , (select PRIMARY_LEDGER_ID BU from FUN_ALL_BUSINESS_UNITS_V BU where SAD.ORG_ID = BU.BU_ID ) LEDGER_ID , SAD.ORG_ID BUSINESS_UNIT_ID , NULL DESTINATION_TYPE_CODE , NULL DOCUMENT_TYPE_CODE , NULL TRANSACTION_SOURCE_CODE , 'EXPENSE_ITEM' TRANSACTION_SUBTYPE_CODE , NULL JE_SOURCE_CODE , NULL JE_CATEGORY_CODE , SA.END_DATE BUDGET_DATE , SA.END_DATE ACCOUNTING_DATE , NULL ENCUMBRANCE_TYPE_CODE , SA.FUNCTIONAL_CURRENCY_CODE ENTERED_CURRENCY , NULL UOM_CODE , NULL QUANTITY , NULL PRICE , (case when SA.CREATION_METHOD_CODE ='CLOSED' THEN -1* SAL.CLOSED_AMOUNT when SA.CREATION_METHOD_CODE ='CANCEL' THEN SAL.CLOSED_AMOUNT else exm_create_payables_doc.GetConvertedAmount(SA.ORG_ID, SAL.REQUESTED_CURRENCY_CODE, SA.FUNCTIONAL_CURRENCY_CODE, SAD.AUTHORIZED_AMOUNT, sysdate, SAL.exchange_rate) end )ENTERED_AMOUNT , null CONVERSION_TYPE_CODE , null CONVERSION_DATE , exm_create_payables_doc.GetConvertedAmount(SA.ORG_ID, SAL.REQUESTED_CURRENCY_CODE, SA.FUNCTIONAL_CURRENCY_CODE, SAD.AUTHORIZED_AMOUNT, sysdate, SAL.exchange_rate) LEDGER_AMOUNT , TO_DATE(NULL) LIQUIDATION_DATE , NULL LIQUIDATION_QUANTITY , NULL LIQUIDATION_AMOUNT , NULL LIQUIDATION_TRANS_TYPE_CODE , NULL LIQUIDATION_LINE_ID_1 , NULL LIQUIDATION_LINE_ID_2 , NULL LIQUIDATION_LINE_ID_3 , NULL LIQUIDATION_LINE_ID_4 , NULL LIQUIDATION_LINE_ID_5 , NULL LIQUIDATION_LINE_ID_6 , NULL STATISTICAL_AMOUNT , NULL BUDGET_CCID , NULL VENDOR_ID , NULL INVENTORY_ITEM_ID , NULL ORDER_TYPE_INFO , SAD.CODE_COMBINATION_ID CODE_COMBINATION_ID , SAD.PJC_PROJECT_ID PJC_PROJECT_ID , SAD.PJC_TASK_ID PJC_TASK_ID , NULL PJC_RESOURCE_ID , SAD.PJC_CONTRACT_ID PJC_CONTRACT_ID , SAD.PJC_CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID , SAD.PJC_FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID , NVL(SAD.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG , NVL(SAD.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG , SAD.PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID , SAD.PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE , SAD.PJC_ORGANIZATION_ID PJC_ORGANIZATION_ID , SAD.PJC_WORK_TYPE_ID PJC_WORK_TYPE_ID , NULL CONTROL_BUDGET_ID , SAD.PJC_RESERVED_ATTRIBUTE1 PJC_FUNDING_SOURCE FROM EXM_SPEND_AUTHORIZATIONS SA , EXM_SPEND_AUTH_LINES SAL , EXM_SPEND_AUTH_DISTS SAD , EXM_EXCH_RATE_OPTIONS EO WHERE SA.SPEND_AUTHORIZATION_ID = SAL.SPEND_AUTHORIZATION_ID AND SAL.SPEND_AUTH_LINE_ID = SAD.SPEND_AUTH_LINE_ID AND EO.ORG_ID(+) = SAD.ORG_ID AND 1 = case when SA.CREATION_METHOD_CODE = 'CLOSED' and SAL.CLOSED_AMOUNT = 0 then 0 else 1 end