AP_INVOICE_EXTRACT_DETAILS_V

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name

EVENT_ID LINE_NUMBER DEFERRED_OPTION DEFERRED_START_DATE DEFERRED_END_DATE DEFERRED_PERIOD_TYPE DEFERRED_NUMBER_OF_PERIODS DEFERRED_ACCRUAL_CCID DEFERRED_START_DATE_DIST DEFERRED_END_DATE_DIST DEFERRED_ACCRUAL_CCID_DIST INV_DISTRIBUTION_IDENTIFIER DIST_ACCOUNT_REVERSAL_OPTION AID_ACCOUNTING_DATE AID_AMOUNT AID_ASSETS_TRACKING_FLAG AID_ATTRIBUTE_CATEGORY AID_ATTRIBUTE1 AID_ATTRIBUTE10 AID_ATTRIBUTE11 AID_ATTRIBUTE12 AID_ATTRIBUTE13 AID_ATTRIBUTE14 AID_ATTRIBUTE15 AID_ATTRIBUTE2 AID_ATTRIBUTE3 AID_ATTRIBUTE4 AID_ATTRIBUTE5 AID_ATTRIBUTE6 AID_ATTRIBUTE7 AID_ATTRIBUTE8 AID_ATTRIBUTE9 AID_AWARD_ID AID_DESCRIPTION AID_DIST_CCID AID_DISTRIBUTION_LINE_NUMBER AID_INCOME_TAX_REGION AID_MERCHANT_DOCUMENT_NUMBER AID_MERCHANT_REFERENCE AID_MERCHANT_TAX_REG_NUMBER AID_MERCHANT_TAXPAYER_ID AID_PERIOD_NAME AID_REFERENCE_1 AID_REFERENCE_2 AID_STAT_AMOUNT AID_TAX_RECOVERABLE_FLAG AID_DETAIL_TAX_LINE_ID AID_TYPE_1099 AP_AWT_GROUP_NAME AIL_LINE_TYPE_LOOKUP_CODE AIL_DESCRIPTION AIL_LINE_SOURCE AIL_LINE_GROUP_NUMBER AIL_ITEM_DESCRIPTION AIL_ACCOUNT_SEGMENT AIL_BALANCING_SEGMENT AIL_COST_CENTER_SEGMENT AIL_OVERLAY_DIST_CODE_CONCAT AIL_DEFAULT_DIST_CCID AIL_ASSET_BOOK_TYPE_CODE AIL_EXPENSE_GROUP AIL_MERCHANT_NAME AIL_ATTRIBUTE_CATEGORY AIL_ATTRIBUTE1 AIL_ATTRIBUTE2 AIL_ATTRIBUTE3 AIL_ATTRIBUTE4 AIL_ATTRIBUTE5 AIL_ATTRIBUTE6 AIL_ATTRIBUTE7 AIL_ATTRIBUTE8 AIL_ATTRIBUTE9 AIL_ATTRIBUTE10 AIL_ATTRIBUTE11 AIL_ATTRIBUTE12 AIL_ATTRIBUTE13 AIL_ATTRIBUTE14 AIL_ATTRIBUTE15 AIL_GLOBAL_ATTRIBUTE_CATEGORY AIL_GLOBAL_ATTRIBUTE1 AIL_GLOBAL_ATTRIBUTE2 AIL_GLOBAL_ATTRIBUTE3 AIL_GLOBAL_ATTRIBUTE4 AIL_GLOBAL_ATTRIBUTE5 AIL_GLOBAL_ATTRIBUTE6 AIL_GLOBAL_ATTRIBUTE7 AIL_GLOBAL_ATTRIBUTE8 AIL_GLOBAL_ATTRIBUTE9 AIL_GLOBAL_ATTRIBUTE10 AIL_GLOBAL_ATTRIBUTE11 AIL_GLOBAL_ATTRIBUTE12 AIL_GLOBAL_ATTRIBUTE13 AIL_GLOBAL_ATTRIBUTE14 AIL_GLOBAL_ATTRIBUTE15 AIL_GLOBAL_ATTRIBUTE16 AIL_GLOBAL_ATTRIBUTE17 AIL_GLOBAL_ATTRIBUTE18 AIL_GLOBAL_ATTRIBUTE19 AIL_GLOBAL_ATTRIBUTE20 AIL_REFERENCE_KEY2 AIL_REFERENCE_KEY3 AIL_REFERENCE_KEY4 AIL_REFERENCE_KEY5 REC_NREC_TAX_DIST_ID SUMMARY_TAX_LINE_ID TAX_LINE_ID TRX_LINE_DIST_ID DEF_REC_SETTLEMENT_OPTION_CODE TAX_POINT_BASIS TAX_ACCOUNT_CCID AID_ASSETS_ADDITION_FLAG AID_LINE_TYPE_LOOKUP_CODE AID_REVERSAL_FLAG AID_GLOBAL_ATTRIBUTE_CAT AID_GLOBAL_ATTRIBUTE1 AID_GLOBAL_ATTRIBUTE2 AID_GLOBAL_ATTRIBUTE3 AID_GLOBAL_ATTRIBUTE4 AID_GLOBAL_ATTRIBUTE5 AID_GLOBAL_ATTRIBUTE6 AID_GLOBAL_ATTRIBUTE7 AID_GLOBAL_ATTRIBUTE8 AID_GLOBAL_ATTRIBUTE9 AID_GLOBAL_ATTRIBUTE10 AID_GLOBAL_ATTRIBUTE11 AID_GLOBAL_ATTRIBUTE12 AID_GLOBAL_ATTRIBUTE13 AID_GLOBAL_ATTRIBUTE14 AID_GLOBAL_ATTRIBUTE15 AID_GLOBAL_ATTRIBUTE16 AID_GLOBAL_ATTRIBUTE17 AID_GLOBAL_ATTRIBUTE18 AID_GLOBAL_ATTRIBUTE19 AID_GLOBAL_ATTRIBUTE20 AID_CANCELLATION_FLAG AID_INVOICE_LINE_NUMBER AID_INVOICE_DIST_ID AID_PARENT_REVERSAL_ID AID_AWT_FLAG ASAT_LIAB_CCID ZRND_TAX_DIST_ID SELF_ASSESSED_TAX_FLAG SELF_ASSESSED_TAX_ACCOUNT AWT_RELATED_DIST_ACCOUNT AWT_AT_PMT_TIME AIL_BASE_AMOUNT AIL_AMOUNT AID_ROUNDING_AMOUNT INV_DIST_BASE_AMOUNT MPA_BASE_AMOUNT MPA_ZERO_AMOUNT AID_BASE_AMT_NO_ROUND DISTRIBUTION_LINK_TYPE PO_DISTRIBUTION_ID BUS_FLOW_AP_APP_ID BUS_FLOW_INV_DIST_TYPE BUS_FLOW_INV_ENTITY_CODE BUS_FLOW_INV_DIST_ID BUS_FLOW_INV_ID BUS_FLOW_PO_APP_ID BUS_FLOW_PO_DIST_TYPE BUS_FLOW_PO_ENTITY_CODE BUS_FLOW_PO_DIST_ID BUS_FLOW_PO_DOC_ID AID_QUANTITY_VARIANCE AID_BASE_QUANTITY_VARIANCE AID_AMOUNT_VARIANCE AID_BASE_AMOUNT_VARIANCE AID_RET_RELATED_DIST_CCID BUS_FLOW_RET_INV_ID BUS_FLOW_RET_INV_DIST_ID AID_FINAL_RELEASE_ROUNDING AIL_UNIT_PRICE AIL_QUANTITY_INVOICED AID_UNIT_PRICE AID_QUANTITY_INVOICED AID_PARENT_INVOICE_ID AID_FINAL_MATCH_FLAG AID_EXTRA_PO_ERV ENCUMBRANCE_BASE_AMOUNT ENCUMBRANCE_AMOUNT VARIANCE_PARENT_DIST_ID CHARGE_APPLICABLE_TO_DIST_ID ALLOC_TO_MAIN_DIST_ID AID_ENCUMBERED_FLAG POD_PO_DISTRIBUTION_ID POD_ENCUMBERED_FLAG POD_ENCUMBERED_AMOUNT POD_GL_ENCUMBERED_DATE POD_GL_ENCUMBERED_PERIOD_NAME POD_ATTRIBUTE_CATEGORY POD_ATTRIBUTE1 POD_ATTRIBUTE2 POD_ATTRIBUTE3 POD_ATTRIBUTE4 POD_ATTRIBUTE5 POD_ATTRIBUTE6 POD_ATTRIBUTE7 POD_ATTRIBUTE8 POD_ATTRIBUTE9 POD_ATTRIBUTE10 POD_ATTRIBUTE11 POD_ATTRIBUTE12 POD_ATTRIBUTE13 POD_ATTRIBUTE14 POD_ATTRIBUTE15 POD_DISTRIBUTION_NUM POD_ACCRUE_ON_RECEIPT_FLAG POD_CCID PO_VARIANCE_ACCOUNT RELATED_INV_DIST_DEST_TYPE POD_PO_HEADER_ID POD_PO_LINE_ID POD_LINE_LOCATION_ID AIL_REFERENCE_KEY1 AIL_PRODUCT_TABLE OVERRIDE_ACCTD_AMT_FLAG ORG_ID LEDGER_ID PO_RCV_CONVERSION_RATE PO_RCV_CONVERSION_DATE PO_RCV_CONVERSION_RATE_TYPE LCM_ENABLED_FLAG DATA_SET_ID ENCUMBRANCE_TYPE_ID INVOICE_ENCUMBRANCE_TYPE_ID SUBLEDGER_BC_COMPLETE_STATUS VARIANCE_PARENT_DIST_CCID BACKING_BURDEN_DISTRIBUTION_ID AID_CHARGE_TO_ITEM_CCID AIL_CREATION_DATE AIL_CREATED_BY AIL_LAST_UPDATE_DATE AIL_LAST_UPDATED_BY AIL_ACCOUNTING_DATE AIL_ASSETS_TRACKING_FLAG AIL_INCOME_TAX_REGION AIL_TYPE_1099 AIL_STAT_AMOUNT AIL_ATTRIBUTE_NUMBER1 AIL_ATTRIBUTE_NUMBER2 AIL_ATTRIBUTE_NUMBER3 AIL_ATTRIBUTE_NUMBER4 AIL_ATTRIBUTE_NUMBER5 AIL_ATTRIBUTE_DATE1 AIL_ATTRIBUTE_DATE2 AIL_ATTRIBUTE_DATE3 AIL_ATTRIBUTE_DATE4 AIL_ATTRIBUTE_DATE5 AIL_GLOBAL_ATTRIBUTE_NUM1 AIL_GLOBAL_ATTRIBUTE_NUM2 AIL_GLOBAL_ATTRIBUTE_NUM3 AIL_GLOBAL_ATTRIBUTE_NUM4 AIL_GLOBAL_ATTRIBUTE_NUM5 AIL_GLOBAL_ATTRIBUTE_DATE1 AIL_GLOBAL_ATTRIBUTE_DATE2 AIL_GLOBAL_ATTRIBUTE_DATE3 AIL_GLOBAL_ATTRIBUTE_DATE4 AIL_GLOBAL_ATTRIBUTE_DATE5

Query

SQL_Statement

SELECT AID.accounting_event_id Event_Id ,AID.invoice_distribution_id Line_Number ,AIL.deferred_acctg_flag Deferred_Option ,AIL.def_acctg_start_date Deferred_Start_Date ,AIL.def_acctg_end_date Deferred_End_Date ,AIL.def_acctg_period_type Deferred_Period_Type ,AIL.def_acctg_number_of_periods Deferred_Number_of_Periods ,AIL.def_acctg_accrual_ccid Deferred_Accrual_CCID ,AID.def_acctg_start_date Deferred_Start_Date_Dist ,AID.def_acctg_end_date Deferred_End_Date_Dist ,AID.def_acctg_accrual_ccid Deferred_Accrual_CCID_Dist ,AID.invoice_distribution_id Inv_Distribution_Identifier ,decode(AIL.GLOBAL_ATTRIBUTE1 , '*UsFedInv*', 'N', decode(NVL(AIL.Historical_Flag, 'N'), 'Y', 'N', decode(nvl(AID.cancellation_flag,'N'), 'Y', decode(nvl(AID.Parent_Reversal_id,-99), -99,'N','Y'), 'N'))) Dist_Account_Reversal_Option ,AID.accounting_date AID_accounting_date ,AID.amount AID_amount ,AID.assets_tracking_flag AID_assets_tracking_flag ,AID.attribute_category AID_attribute_category ,AID.attribute1 AID_attribute1 ,AID.attribute10 AID_attribute10 ,AID.attribute11 AID_attribute11 ,AID.attribute12 AID_attribute12 ,AID.attribute13 AID_attribute13 ,AID.attribute14 AID_attribute14 ,AID.attribute15 AID_attribute15 ,AID.attribute2 AID_attribute2 ,AID.attribute3 AID_attribute3 ,AID.attribute4 AID_attribute4 ,AID.attribute5 AID_attribute5 ,AID.attribute6 AID_attribute6 ,AID.attribute7 AID_attribute7 ,AID.attribute8 AID_attribute8 ,AID.attribute9 AID_attribute9 ,AID.award_id AID_award_id ,AID.description AID_description ,AID.dist_code_combination_id AID_dist_CCID ,AID.distribution_line_number AID_distribution_line_number ,AID.income_tax_region AID_income_tax_region ,AID.merchant_document_number AID_merchant_document_number ,AID.merchant_reference AID_merchant_reference ,AID.merchant_tax_reg_number AID_merchant_tax_reg_number ,AID.merchant_taxpayer_id AID_merchant_taxpayer_id ,AID.period_name AID_period_name ,AID.reference_1 AID_reference_1 ,AID.reference_2 AID_reference_2 ,AID.stat_amount AID_stat_amount ,AID.tax_recoverable_flag AID_tax_recoverable_flag ,AID.detail_tax_line_id AID_detail_tax_line_id ,AID.type_1099 AID_type_1099 ,ZCG.condition_group_name AP_AWT_group_name ,AIL.line_type_lookup_code AIL_LINE_TYPE_LOOKUP_CODE ,AIL.description AIL_DESCRIPTION ,AIL.line_source AIL_LINE_SOURCE ,AIL.line_group_number AIL_LINE_GROUP_NUMBER ,AIL.item_description AIL_ITEM_DESCRIPTION ,AIL.account_segment AIL_ACCOUNT_SEGMENT ,AIL.balancing_segment AIL_BALANCING_SEGMENT ,AIL.cost_center_segment AIL_COST_CENTER_SEGMENT ,AIL.overlay_dist_code_concat AIL_OVERLAY_DIST_CODE_CONCAT ,AIL.default_dist_ccid AIL_DEFAULT_DIST_CCID ,AIL.asset_book_type_code AIL_ASSET_BOOK_TYPE_CODE ,AIL.expense_group AIL_EXPENSE_GROUP ,AIL.merchant_name AIL_MERCHANT_NAME ,AIL.attribute_category AIL_ATTRIBUTE_CATEGORY ,AIL.attribute1 AIL_ATTRIBUTE1 ,AIL.attribute2 AIL_ATTRIBUTE2 ,AIL.attribute3 AIL_ATTRIBUTE3 ,AIL.attribute4 AIL_ATTRIBUTE4 ,AIL.attribute5 AIL_ATTRIBUTE5 ,AIL.attribute6 AIL_ATTRIBUTE6 ,AIL.attribute7 AIL_ATTRIBUTE7 ,AIL.attribute8 AIL_ATTRIBUTE8 ,AIL.attribute9 AIL_ATTRIBUTE9 ,AIL.attribute10 AIL_ATTRIBUTE10 ,AIL.attribute11 AIL_ATTRIBUTE11 ,AIL.attribute12 AIL_ATTRIBUTE12 ,AIL.attribute13 AIL_ATTRIBUTE13 ,AIL.attribute14 AIL_ATTRIBUTE14 ,AIL.attribute15 AIL_ATTRIBUTE15 ,AIL.global_attribute_category AIL_GLOBAL_ATTRIBUTE_CATEGORY ,AIL.global_attribute1 AIL_GLOBAL_ATTRIBUTE1 ,AIL.global_attribute2 AIL_GLOBAL_ATTRIBUTE2 ,AIL.global_attribute3 AIL_GLOBAL_ATTRIBUTE3 ,AIL.global_attribute4 AIL_GLOBAL_ATTRIBUTE4 ,AIL.global_attribute5 AIL_GLOBAL_ATTRIBUTE5 ,AIL.global_attribute6 AIL_GLOBAL_ATTRIBUTE6 ,AIL.global_attribute7 AIL_GLOBAL_ATTRIBUTE7 ,AIL.global_attribute8 AIL_GLOBAL_ATTRIBUTE8 ,AIL.global_attribute9 AIL_GLOBAL_ATTRIBUTE9 ,AIL.global_attribute10 AIL_GLOBAL_ATTRIBUTE10 ,AIL.global_attribute11 AIL_GLOBAL_ATTRIBUTE11 ,AIL.global_attribute12 AIL_GLOBAL_ATTRIBUTE12 ,AIL.global_attribute13 AIL_GLOBAL_ATTRIBUTE13 ,AIL.global_attribute14 AIL_GLOBAL_ATTRIBUTE14 ,AIL.global_attribute15 AIL_GLOBAL_ATTRIBUTE15 ,AIL.global_attribute16 AIL_GLOBAL_ATTRIBUTE16 ,AIL.global_attribute17 AIL_GLOBAL_ATTRIBUTE17 ,AIL.global_attribute18 AIL_GLOBAL_ATTRIBUTE18 ,AIL.global_attribute19 AIL_GLOBAL_ATTRIBUTE19 ,AIL.global_attribute20 AIL_GLOBAL_ATTRIBUTE20 ,AIL.reference_key2 AIL_REFERENCE_KEY2 ,AIL.reference_key3 AIL_REFERENCE_KEY3 ,AIL.reference_key4 AIL_REFERENCE_KEY4 ,AIL.reference_key5 AIL_REFERENCE_KEY5 ,ZDT.REC_NREC_TAX_DIST_ID REC_NREC_TAX_DIST_ID ,AIL.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID ,ZDT.TAX_LINE_ID TAX_LINE_ID ,ZDT.TRX_LINE_DIST_ID TRX_LINE_DIST_ID ,ZDT.DEF_REC_SETTLEMENT_OPTION_CODE DEF_REC_SETTLEMENT_OPTION_CODE ,ZDT.TAX_POINT_BASIS TAX_POINT_BASIS ,accounts.TAX_ACCOUNT_CCID TAX_ACCOUNT_CCID ,AID.assets_addition_flag AID_ASSETS_ADDITION_FLAG ,AID.line_type_lookup_code AID_LINE_TYPE_LOOKUP_CODE ,AID.reversal_flag AID_REVERSAL_FLAG ,AID.global_attribute_category AID_GLOBAL_ATTRIBUTE_CAT ,AID.global_attribute1 AID_GLOBAL_ATTRIBUTE1 ,AID.global_attribute2 AID_GLOBAL_ATTRIBUTE2 ,AID.global_attribute3 AID_GLOBAL_ATTRIBUTE3 ,AID.global_attribute4 AID_GLOBAL_ATTRIBUTE4 ,AID.global_attribute5 AID_GLOBAL_ATTRIBUTE5 ,AID.global_attribute6 AID_GLOBAL_ATTRIBUTE6 ,AID.global_attribute7 AID_GLOBAL_ATTRIBUTE7 ,AID.global_attribute8 AID_GLOBAL_ATTRIBUTE8 ,AID.global_attribute9 AID_GLOBAL_ATTRIBUTE9 ,AID.global_attribute10 AID_GLOBAL_ATTRIBUTE10 ,AID.global_attribute11 AID_GLOBAL_ATTRIBUTE11 ,AID.global_attribute12 AID_GLOBAL_ATTRIBUTE12 ,AID.global_attribute13 AID_GLOBAL_ATTRIBUTE13 ,AID.global_attribute14 AID_GLOBAL_ATTRIBUTE14 ,AID.global_attribute15 AID_GLOBAL_ATTRIBUTE15 ,AID.global_attribute16 AID_GLOBAL_ATTRIBUTE16 ,AID.global_attribute17 AID_GLOBAL_ATTRIBUTE17 ,AID.global_attribute18 AID_GLOBAL_ATTRIBUTE18 ,AID.global_attribute19 AID_GLOBAL_ATTRIBUTE19 ,AID.global_attribute20 AID_GLOBAL_ATTRIBUTE20 ,AID.cancellation_flag AID_CANCELLATION_FLAG ,AID.invoice_line_number AID_INVOICE_LINE_NUMBER ,AID.invoice_distribution_id AID_invoice_dist_id ,AID.parent_reversal_id AID_parent_reversal_id ,AID.awt_flag AID_AWT_Flag ,-1 ASAT_liab_ccid ,AID.detail_tax_dist_id ZRND_tax_dist_id ,'N' self_assessed_tax_flag ,-1 self_assessed_tax_account ,AWT_RELATED_IDS.dist_code_combination_id AWT_Related_DIST_ACCOUNT ,decode(AID.awt_invoice_payment_id, null,'N','Y') AWT_AT_PMT_TIME ,nvl(AIL.base_Amount,AIL.amount) AIL_BASE_Amount ,AIL.amount AIL_amount ,NVL(AID.rounding_amt, 0) AID_Rounding_amount ,nvl(AID.Base_Amount, AID.amount) Inv_Dist_Base_Amount ,nvl(AID.base_Amount,AID.amount)+ (CASE WHEN aid.po_distribution_id is not null AND aid.line_type_lookup_code IN ('ITEM','NONREC_TAX') AND aid.def_acctg_start_date is not NULL THEN nvl((Select nvl(AID2.base_amount,AID2.amount) FROM ap_invoice_distributions_all aid2 WHERE aid2.invoice_id = aid.invoice_id AND aid2.related_id = aid.invoice_distribution_id AND aid2.amount = 0 AND aid2.line_type_lookup_code IN ('ERV','TERV') AND ROWNUM = 1),0) ELSE 0 END) MPA_BASE_Amount ,0 MPA_ZERO_AMOUNT ,(nvl(AID.Base_Amount, AID.amount) - nvl(AID.rounding_amt,0)) AID_Base_Amt_No_Round ,'AP_INV_DIST' Distribution_Link_Type ,AID.po_distribution_id PO_distribution_id ,200 Bus_Flow_AP_App_Id ,'AP_INV_DIST' Bus_Flow_Inv_Dist_Type ,'AP_INVOICES' Bus_Flow_Inv_Entity_Code ,AID.invoice_distribution_Id Bus_Flow_Inv_Dist_Id ,AID.invoice_id Bus_Flow_Inv_Id ,201 Bus_Flow_PO_App_Id ,'PO_DISTRIBUTIONS_ALL' Bus_Flow_PO_Dist_Type ,'PO' Bus_Flow_PO_Entity_Code ,AID.po_distribution_id Bus_Flow_PO_Dist_Id ,pod.po_header_id Bus_Flow_PO_Doc_Id ,AID.quantity_variance AID_quantity_variance ,nvl(AID.base_quantity_variance,AID.quantity_variance) AID_base_quantity_variance ,AID.amount_variance AID_amount_variance ,nvl(AID.base_amount_variance,AID.amount_variance) AID_base_amount_variance ,decode(AID.line_type_lookup_code, 'RETAINAGE', AIDR.dist_code_combination_id, null) AID_ret_related_dist_ccid ,decode(AIL.line_type_lookup_code, 'RETAINAGE RELEASE', AIL.retained_invoice_id, null) Bus_Flow_Ret_Inv_Id ,decode(AIL.line_type_lookup_code, 'RETAINAGE RELEASE', AID.retained_invoice_dist_id, null) Bus_Flow_Ret_Inv_Dist_Id ,NVL(AID.final_release_rounding, 0) AID_Final_Release_Rounding ,AIL.unit_price AIL_unit_price ,AIL.quantity_invoiced AIL_quantity_invoiced ,AID.unit_price AID_unit_price ,AID.quantity_invoiced AID_quantity_invoiced ,AID.parent_invoice_id AID_parent_invoice_id ,AID.final_match_flag AID_final_match_flag ,NVL(AID.EXTRA_PO_ERV,0) AID_EXTRA_PO_ERV ,NVL(AID.base_amount,AID.amount) -NVL(AID.base_amount_variance,nvl(AID.amount_variance,0)) -NVL(AID.base_quantity_variance,nvl(AID.quantity_variance,0)) ENCUMBRANCE_BASE_AMOUNT ,NVL(AID.amount,0) - NVL(AID.amount_variance,0) - NVL(AID.quantity_variance,0) ENCUMBRANCE_AMOUNT ,AID.related_id VARIANCE_PARENT_DIST_ID ,AID.CHARGE_APPLICABLE_TO_DIST_ID CHARGE_APPLICABLE_TO_DIST_ID ,NVL(NVL(AID.charge_applicable_to_dist_id, AID.related_id), AID.invoice_distribution_id) ALLOC_TO_MAIN_DIST_ID ,AID.encumbered_flag AID_ENCUMBERED_FLAG ,POD.PO_DISTRIBUTION_ID POD_PO_DISTRIBUTION_ID ,POD.ENCUMBERED_FLAG POD_ENCUMBERED_FLAG ,POD.ENCUMBERED_AMOUNT POD_ENCUMBERED_AMOUNT ,POD.GL_ENCUMBERED_DATE POD_GL_ENCUMBERED_DATE ,POD.GL_Encumbered_Period_Name POD_GL_Encumbered_Period_Name ,POD.ATTRIBUTE_CATEGORY POD_ATTRIBUTE_CATEGORY ,POD.ATTRIBUTE1 POD_ATTRIBUTE1 ,POD.ATTRIBUTE2 POD_ATTRIBUTE2 ,POD.ATTRIBUTE3 POD_ATTRIBUTE3 ,POD.ATTRIBUTE4 POD_ATTRIBUTE4 ,POD.ATTRIBUTE5 POD_ATTRIBUTE5 ,POD.ATTRIBUTE6 POD_ATTRIBUTE6 ,POD.ATTRIBUTE7 POD_ATTRIBUTE7 ,POD.ATTRIBUTE8 POD_ATTRIBUTE8 ,POD.ATTRIBUTE9 POD_ATTRIBUTE9 ,POD.ATTRIBUTE10 POD_ATTRIBUTE10 ,POD.ATTRIBUTE11 POD_ATTRIBUTE11 ,POD.ATTRIBUTE12 POD_ATTRIBUTE12 ,POD.ATTRIBUTE13 POD_ATTRIBUTE13 ,POD.ATTRIBUTE14 POD_ATTRIBUTE14 ,POD.ATTRIBUTE15 POD_ATTRIBUTE15 ,POD.DISTRIBUTION_NUM POD_DISTRIBUTION_NUM , CASE WHEN ( AID.LINE_TYPE_LOOKUP_CODE ='TERV' AND ZDT.TAX_POINT_BASIS = 'DELIVERY' AND AID.PO_DISTRIBUTION_ID IS NULL ) THEN 'Y' ELSE POD.ACCRUE_ON_RECEIPT_FLAG END POD_ACCRUE_ON_RECEIPT_FLAG ,POD.CODE_COMBINATION_ID POD_CCID ,POD.variance_Account_id PO_variance_Account ,POD.destination_type_code Related_Inv_Dist_Dest_Type ,POD.po_header_id POD_PO_HEADER_ID ,POD.po_line_id POD_PO_LINE_ID ,POD.line_location_id POD_LINE_LOCATION_ID /* ,CASE WHEN(fsp.req_encumbrance_flag = 'Y' OR fsp.purch_encumbrance_flag = 'Y') THEN psa_bc_xla_pub.get_sla_notupgraded_flag( decode(nvl(aid.po_distribution_id, -99), -99, 200, 201), decode(nvl(aid.po_distribution_id, -99), -99, 'AP_INVOICES', decode(pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE')), decode(nvl(aid.po_distribution_id, -99), -99, aid.invoice_id, decode(pod.po_release_id, NULL, pod.po_header_id, pod.po_release_id)), decode(nvl(aid.po_distribution_id, -99), -99, 'AP_INV_DIST', 'PO_DISTRIBUTIONS_ALL'), decode(nvl(aid.po_distribution_id, -99), -99, aid.parent_reversal_id, aid.po_distribution_id)) ELSE 'N' END UPG_AP_ENCUM_OPTION */ ,AIL.REFERENCE_KEY1 AIL_REFERENCE_KEY1 ,AIL.PRODUCT_TABLE AIL_PRODUCT_TABLE ,'Y' Override_Acctd_Amt_Flag ,zdt.INTERNAL_ORGANIZATION_ID ORG_ID ,zdt.LEDGER_ID LEDGER_ID ,DECODE(RCV.TRANSACTION_ID, NULL, NVL(POD.RATE, POH.RATE), RCV.CURRENCY_CONVERSION_RATE) PO_RCV_CONVERSION_RATE ,DECODE(RCV.TRANSACTION_ID, NULL, NVL(POD.RATE_DATE, POD.CREATION_DATE), NVL(RCV.CURRENCY_CONVERSION_DATE, RCV.TRANSACTION_DATE)) PO_RCV_CONVERSION_DATE ,DECODE(RCV.TRANSACTION_ID, NULL, POH.RATE_TYPE, RCV.CURRENCY_CONVERSION_TYPE) PO_RCV_CONVERSION_RATE_TYPE, AIL.lcm_enabled_flag, AID.data_set_id DATA_SET_ID, GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID, GLET1.encumbrance_type_id INVOICE_ENCUMBRANCE_TYPE_ID, CASE WHEN (AID.funds_status LIKE 'RESERVED%' AND AID.funds_status NOT IN ( 'RESERVED_NOT_APPLICABLE', 'NOT_REQUIRED_BY_SUBLEDGER') AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('REC_TAX', 'AWT', 'ERV', 'TERV') ) THEN 'Y' WHEN AID.funds_status = 'RESERVED_NOT_APPLICABLE' then CASE WHEN AID.PJC_PROJECT_ID is not null and POD.destination_type_code in('INVENTORY','MANUFACTURING') THEN 'Y' ELSE 'N' END WHEN AID.funds_status = 'NOT_ATTEMPTED' then NULL WHEN AID.funds_status = 'NOT_REQUIRED_BY_SUBLEDGER' THEN 'R' ELSE NULL END SUBLEDGER_BC_COMPLETE_STATUS ,AID_ERV_REL.dist_code_combination_id VARIANCE_PARENT_DIST_CCID ,-99 BACKING_BURDEN_DISTRIBUTION_ID ,CASE WHEN AID.line_type_lookup_code in ( 'REC_TAX' , 'NONREC_TAX' , 'TRV' ) THEN (select item.dist_code_combination_id from ap_invoice_distributions_all item Where item.invoice_distribution_id = aid.charge_applicable_to_dist_id) ELSE NULL END AID_CHARGE_TO_ITEM_CCID , cast(ail.creation_date as DATE) ail_creation_date , ail.created_by ail_created_by , cast(ail.last_update_date as DATE) ail_last_update_date , ail.last_updated_by ail_last_updated_by ,AIL.ACCOUNTING_DATE AIL_ACCOUNTING_DATE ,AIL.ASSETS_TRACKING_FLAG AIL_ASSETS_TRACKING_FLAG ,AIL.INCOME_TAX_REGION AIL_INCOME_TAX_REGION ,AIL.TYPE_1099 AIL_TYPE_1099 ,AIL.STAT_AMOUNT AIL_STAT_AMOUNT ,AIL.ATTRIBUTE_NUMBER1 AIL_ATTRIBUTE_NUMBER1 ,AIL.ATTRIBUTE_NUMBER2 AIL_ATTRIBUTE_NUMBER2 ,AIL.ATTRIBUTE_NUMBER3 AIL_ATTRIBUTE_NUMBER3 ,AIL.ATTRIBUTE_NUMBER4 AIL_ATTRIBUTE_NUMBER4 ,AIL.ATTRIBUTE_NUMBER5 AIL_ATTRIBUTE_NUMBER5 ,AIL.ATTRIBUTE_DATE1 AIL_ATTRIBUTE_DATE1 ,AIL.ATTRIBUTE_DATE2 AIL_ATTRIBUTE_DATE2 ,AIL.ATTRIBUTE_DATE3 AIL_ATTRIBUTE_DATE3 ,AIL.ATTRIBUTE_DATE4 AIL_ATTRIBUTE_DATE4 ,AIL.ATTRIBUTE_DATE5 AIL_ATTRIBUTE_DATE5 ,AIL.GLOBAL_ATTRIBUTE_NUMBER1 AIL_GLOBAL_ATTRIBUTE_NUM1 ,AIL.GLOBAL_ATTRIBUTE_NUMBER2 AIL_GLOBAL_ATTRIBUTE_NUM2 ,AIL.GLOBAL_ATTRIBUTE_NUMBER3 AIL_GLOBAL_ATTRIBUTE_NUM3 ,AIL.GLOBAL_ATTRIBUTE_NUMBER4 AIL_GLOBAL_ATTRIBUTE_NUM4 ,AIL.GLOBAL_ATTRIBUTE_NUMBER5 AIL_GLOBAL_ATTRIBUTE_NUM5 ,AIL.GLOBAL_ATTRIBUTE_DATE1 AIL_GLOBAL_ATTRIBUTE_DATE1 ,AIL.GLOBAL_ATTRIBUTE_DATE2 AIL_GLOBAL_ATTRIBUTE_DATE2 ,AIL.GLOBAL_ATTRIBUTE_DATE3 AIL_GLOBAL_ATTRIBUTE_DATE3 ,AIL.GLOBAL_ATTRIBUTE_DATE4 AIL_GLOBAL_ATTRIBUTE_DATE4 ,AIL.GLOBAL_ATTRIBUTE_DATE5 AIL_GLOBAL_ATTRIBUTE_DATE5 FROM AP_INVOICE_LINES_ALL AIL, AP_INVOICE_DISTRIBUTIONS_ALL AID, AP_INVOICE_DISTRIBUTIONS_ALL AIDR, AP_INVOICE_DISTRIBUTIONS_ALL AID_ERV_REL, ZX_CONDITION_GROUPS_TL ZCG, AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS, PO_DISTRIBUTIONS_ALL POD, GL_ENCUMBRANCE_TYPES_B GLET, GL_ENCUMBRANCE_TYPES_B GLET1, FINANCIALS_SYSTEM_PARAMS_ALL FSP, zx_rec_nrec_dist zdt, zx_accounts accounts, RCV_TRANSACTIONS RCV, PO_HEADERS_ALL POH WHERE AID.invoice_line_number = AIL.line_number AND AID.invoice_id = AIL.invoice_id AND AID.line_type_lookup_code <> 'PREPAY' AND AID.awt_group_id = ZCG.condition_group_id(+) AND userenv('LANG') = ZCG.language(+) AND AID.awt_related_id = AWT_RELATED_IDS.invoice_distribution_id(+) AND AID.related_retainage_dist_id = AIDR.invoice_distribution_id(+) AND AID.po_distribution_id = POD.po_distribution_id(+) AND AID.detail_tax_dist_id = zdt.REC_NREC_TAX_DIST_ID(+) AND GLET.encumbrance_type_code='Obligation' AND GLET.enabled_flag= 'Y' AND GLET1.encumbrance_type_code = 'ORA_Invoice' AND GLET1.enabled_flag = 'Y' AND aid.org_id = fsp.org_id AND accounts.TAX_ACCOUNT_ENTITY_ID (+) = nvl(ZDT.ACCOUNT_SOURCE_TAX_RATE_ID, ZDT.TAX_RATE_ID) AND accounts.TAX_ACCOUNT_ENTITY_CODE (+)= 'RATES' AND accounts.INTERNAL_ORGANIZATION_ID (+)= ZDT.INTERNAL_ORGANIZATION_ID AND accounts.LEDGER_ID (+) = ZDT.LEDGER_ID AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+) AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+) /* bug 14104062 - start */ AND AID_ERV_REL.invoice_id(+) = AID.invoice_id AND AID_ERV_REL.invoice_distribution_id(+) = AID.related_id /* bug 14104062 - end */ UNION ALL SELECT ASAT.accounting_event_id Event_Id ,ASAT.invoice_distribution_id Line_Number ,AIL.deferred_acctg_flag Deferred_Option ,AIL.def_acctg_start_date Deferred_Start_Date ,AIL.def_acctg_end_date Deferred_End_Date ,AIL.def_acctg_period_type Deferred_Period_Type ,AIL.def_acctg_number_of_periods Deferred_Number_of_Periods ,AIL.def_acctg_accrual_ccid Deferred_Accrual_CCID ,ASAT.def_acctg_start_date Deferred_Start_Date_Dist ,ASAT.def_acctg_end_date Deferred_End_Date_Dist ,ASAT.def_acctg_accrual_ccid Deferred_Accrual_CCID_Dist ,ASAT.invoice_distribution_id Inv_Distribution_Identifier ,decode(AIL.GLOBAL_ATTRIBUTE1, '*UsFedInv*', 'N', decode( nvl(ASAT.cancellation_flag,'N'), 'Y', decode(nvl(ASAT.Parent_Reversal_id,-99), -99,'N','Y') , 'N')) Dist_Account_Reversal_Option ,ASAT.accounting_date AID_accounting_date ,ASAT.amount AID_amount ,ASAT.assets_tracking_flag AID_assets_tracking_flag ,ASAT.attribute_category AID_attribute_category ,ASAT.attribute1 AID_attribute1 ,ASAT.attribute10 AID_attribute10 ,ASAT.attribute11 AID_attribute11 ,ASAT.attribute12 AID_attribute12 ,ASAT.attribute13 AID_attribute13 ,ASAT.attribute14 AID_attribute14 ,ASAT.attribute15 AID_attribute15 ,ASAT.attribute2 AID_attribute2 ,ASAT.attribute3 AID_attribute3 ,ASAT.attribute4 AID_attribute4 ,ASAT.attribute5 AID_attribute5 ,ASAT.attribute6 AID_attribute6 ,ASAT.attribute7 AID_attribute7 ,ASAT.attribute8 AID_attribute8 ,ASAT.attribute9 AID_attribute9 ,ASAT.award_id AID_award_id ,ASAT.description AID_description ,ASAT.dist_code_combination_id AID_dist_CCID ,ASAT.distribution_line_number AID_distribution_line_number ,ASAT.income_tax_region AID_income_tax_region ,ASAT.merchant_document_number AID_merchant_document_number ,ASAT.merchant_reference AID_merchant_reference ,ASAT.merchant_tax_reg_number AID_merchant_tax_reg_number ,ASAT.merchant_taxpayer_id AID_merchant_taxpayer_id ,ASAT.period_name AID_period_name ,ASAT.reference_1 AID_reference_1 ,ASAT.reference_2 AID_reference_2 ,ASAT.stat_amount AID_stat_amount ,ASAT.tax_recoverable_flag AID_tax_recoverable_flag ,null AID_detail_tax_line_id ,ASAT.type_1099 AID_type_1099 ,ZCG.condition_group_name AP_AWT_group_name ,AIL.line_type_lookup_code AIL_LINE_TYPE_LOOKUP_CODE ,AIL.description AIL_DESCRIPTION ,AIL.line_source AIL_LINE_SOURCE ,AIL.line_group_number AIL_LINE_GROUP_NUMBER ,AIL.item_description AIL_ITEM_DESCRIPTION ,AIL.account_segment AIL_ACCOUNT_SEGMENT ,AIL.balancing_segment AIL_BALANCING_SEGMENT ,AIL.cost_center_segment AIL_COST_CENTER_SEGMENT ,AIL.overlay_dist_code_concat AIL_OVERLAY_DIST_CODE_CONCAT ,AIL.default_dist_ccid AIL_DEFAULT_DIST_CCID ,AIL.asset_book_type_code AIL_ASSET_BOOK_TYPE_CODE ,AIL.expense_group AIL_EXPENSE_GROUP ,AIL.merchant_name AIL_MERCHANT_NAME ,AIL.attribute_category AIL_ATTRIBUTE_CATEGORY ,AIL.attribute1 AIL_ATTRIBUTE1 ,AIL.attribute2 AIL_ATTRIBUTE2 ,AIL.attribute3 AIL_ATTRIBUTE3 ,AIL.attribute4 AIL_ATTRIBUTE4 ,AIL.attribute5 AIL_ATTRIBUTE5 ,AIL.attribute6 AIL_ATTRIBUTE6 ,AIL.attribute7 AIL_ATTRIBUTE7 ,AIL.attribute8 AIL_ATTRIBUTE8 ,AIL.attribute9 AIL_ATTRIBUTE9 ,AIL.attribute10 AIL_ATTRIBUTE10 ,AIL.attribute11 AIL_ATTRIBUTE11 ,AIL.attribute12 AIL_ATTRIBUTE12 ,AIL.attribute13 AIL_ATTRIBUTE13 ,AIL.attribute14 AIL_ATTRIBUTE14 ,AIL.attribute15 AIL_ATTRIBUTE15 ,AIL.global_attribute_category AIL_GLOBAL_ATTRIBUTE_CATEGORY ,AIL.global_attribute1 AIL_GLOBAL_ATTRIBUTE1 ,AIL.global_attribute2 AIL_GLOBAL_ATTRIBUTE2 ,AIL.global_attribute3 AIL_GLOBAL_ATTRIBUTE3 ,AIL.global_attribute4 AIL_GLOBAL_ATTRIBUTE4 ,AIL.global_attribute5 AIL_GLOBAL_ATTRIBUTE5 ,AIL.global_attribute6 AIL_GLOBAL_ATTRIBUTE6 ,AIL.global_attribute7 AIL_GLOBAL_ATTRIBUTE7 ,AIL.global_attribute8 AIL_GLOBAL_ATTRIBUTE8 ,AIL.global_attribute9 AIL_GLOBAL_ATTRIBUTE9 ,AIL.global_attribute10 AIL_GLOBAL_ATTRIBUTE10 ,AIL.global_attribute11 AIL_GLOBAL_ATTRIBUTE11 ,AIL.global_attribute12 AIL_GLOBAL_ATTRIBUTE12 ,AIL.global_attribute13 AIL_GLOBAL_ATTRIBUTE13 ,AIL.global_attribute14 AIL_GLOBAL_ATTRIBUTE14 ,AIL.global_attribute15 AIL_GLOBAL_ATTRIBUTE15 ,AIL.global_attribute16 AIL_GLOBAL_ATTRIBUTE16 ,AIL.global_attribute17 AIL_GLOBAL_ATTRIBUTE17 ,AIL.global_attribute18 AIL_GLOBAL_ATTRIBUTE18 ,AIL.global_attribute19 AIL_GLOBAL_ATTRIBUTE19 ,AIL.global_attribute20 AIL_GLOBAL_ATTRIBUTE20 ,AIL.reference_key2 AIL_REFERENCE_KEY2 ,AIL.reference_key3 AIL_REFERENCE_KEY3 ,AIL.reference_key4 AIL_REFERENCE_KEY4 ,AIL.reference_key5 AIL_REFERENCE_KEY5 ,ZDT.REC_NREC_TAX_DIST_ID REC_NREC_TAX_DIST_ID ,AIL.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID ,ZDT.TAX_LINE_ID TAX_LINE_ID ,ZDT.TRX_LINE_DIST_ID TRX_LINE_DIST_ID ,ZDT.DEF_REC_SETTLEMENT_OPTION_CODE DEF_REC_SETTLEMENT_OPTION_CODE ,ZDT.TAX_POINT_BASIS TAX_POINT_BASIS ,ACCOUNTS.TAX_ACCOUNT_CCID TAX_ACCOUNT_CCID ,ASAT.assets_addition_flag AID_ASSETS_ADDITION_FLAG ,ASAT.line_type_lookup_code AID_LINE_TYPE_LOOKUP_CODE ,ASAT.reversal_flag AID_REVERSAL_FLAG ,ASAT.global_attribute_category AID_GLOBAL_ATTRIBUTE_CAT ,ASAT.global_attribute1 AID_GLOBAL_ATTRIBUTE1 ,ASAT.global_attribute2 AID_GLOBAL_ATTRIBUTE2 ,ASAT.global_attribute3 AID_GLOBAL_ATTRIBUTE3 ,ASAT.global_attribute4 AID_GLOBAL_ATTRIBUTE4 ,ASAT.global_attribute5 AID_GLOBAL_ATTRIBUTE5 ,ASAT.global_attribute6 AID_GLOBAL_ATTRIBUTE6 ,ASAT.global_attribute7 AID_GLOBAL_ATTRIBUTE7 ,ASAT.global_attribute8 AID_GLOBAL_ATTRIBUTE8 ,ASAT.global_attribute9 AID_GLOBAL_ATTRIBUTE9 ,ASAT.global_attribute10 AID_GLOBAL_ATTRIBUTE10 ,ASAT.global_attribute11 AID_GLOBAL_ATTRIBUTE11 ,ASAT.global_attribute12 AID_GLOBAL_ATTRIBUTE12 ,ASAT.global_attribute13 AID_GLOBAL_ATTRIBUTE13 ,ASAT.global_attribute14 AID_GLOBAL_ATTRIBUTE14 ,ASAT.global_attribute15 AID_GLOBAL_ATTRIBUTE15 ,ASAT.global_attribute16 AID_GLOBAL_ATTRIBUTE16 ,ASAT.global_attribute17 AID_GLOBAL_ATTRIBUTE17 ,ASAT.global_attribute18 AID_GLOBAL_ATTRIBUTE18 ,ASAT.global_attribute19 AID_GLOBAL_ATTRIBUTE19 ,ASAT.global_attribute20 AID_GLOBAL_ATTRIBUTE20 ,ASAT.cancellation_flag AID_CANCELLATION_FLAG ,ASAT.invoice_line_number AID_INVOICE_LINE_NUMBER ,ASAT.invoice_distribution_id AID_invoice_dist_id ,ASAT.parent_reversal_id AID_parent_reversal_id ,ASAT.awt_flag AID_AWT_Flag ,ASAT.SELF_ASSESSED_TAX_LIAB_CCID ASAT_liab_ccid ,ASAT.detail_tax_dist_id ZRND_tax_dist_id ,ASAT.SELF_ASSESSED_FLAG self_assessed_tax_flag ,ASAT.DIST_CODE_COMBINATION_ID self_assessed_tax_account ,-1 awt_related_dist_account ,decode(ASAT.awt_invoice_payment_id, null,'N','Y') AWT_AT_PMT_TIME ,nvl(AIL.base_Amount,AIL.amount) AIL_BASE_Amount ,AIL.amount AIL_amount ,NVL(ASAT.rounding_amt, 0) AID_Rounding_amount ,nvl(ASAT.Base_Amount, ASAT.amount) Inv_Dist_Base_Amount ,0 MPA_BASE_AMOUNT ,0 MPA_ZERO_AMOUNT ,(nvl(ASAT.Base_Amount, ASAT.amount) - nvl(ASAT.rounding_amt,0)) AID_Base_Amt_No_Round ,'AP_INV_DIST' Distribution_Link_Type ,ASAT.po_distribution_id PO_distribution_id ,200 Bus_Flow_AP_App_Id ,'AP_INV_DIST' Bus_Flow_Inv_Dist_Type ,'AP_INVOICES' Bus_Flow_Inv_Entity_Code ,ASAT.invoice_distribution_Id Bus_Flow_Inv_Dist_Id ,ASAT.invoice_id Bus_Flow_Inv_Id ,201 Bus_Flow_PO_App_Id ,'PO_DISTRIBUTIONS_ALL' Bus_Flow_PO_Dist_Type ,'PO' Bus_Flow_PO_Entity_Code ,ASAT.po_distribution_id Bus_Flow_PO_Dist_Id ,pod.po_header_id Bus_Flow_PO_Doc_Id ,ASAT.quantity_variance AID_quantity_variance ,nvl(ASAT.base_quantity_variance, ASAT.quantity_variance) AID_base_quantity_variance ,ASAT.amount_variance AID_amount_variance ,nvl(ASAT.base_amount_variance, ASAT.amount_variance) AID_base_amount_variance ,-1 AID_ret_related_dist_ccid ,-1 Bus_Flow_Ret_Inv_Id ,-1 Bus_Flow_Ret_Inv_Dist_Id ,0 AID_Final_Release_Rounding ,AIL.unit_price AIL_unit_price ,AIL.quantity_invoiced AIL_quantity_invoiced ,ASAT.unit_price AID_unit_price ,ASAT.quantity_invoiced AID_quantity_invoiced ,ASAT.parent_invoice_id AID_parent_invoice_id ,ASAT.final_match_flag AID_final_match_flag ,NVL(ASAT.EXTRA_PO_ERV,0) AID_EXTRA_PO_ERV ,NVL(ASAT.base_amount,ASAT.amount) - NVL(ASAT.base_amount_variance,nvl(ASAT.amount_variance,0)) - NVL(ASAT.base_quantity_variance,nvl(ASAT.quantity_variance,0)) ENCUMBRANCE_BASE_AMOUNT ,NVL(ASAT.amount,0) - NVL(ASAT.amount_variance,0) - NVL(ASAT.quantity_variance,0) ENCUMBRANCE_AMOUNT ,ASAT.related_id VARIANCE_PARENT_DIST_ID ,ASAT.CHARGE_APPLICABLE_TO_DIST_ID CHARGE_APPLICABLE_TO_DIST_ID ,NVL(NVL(ASAT.charge_applicable_to_dist_id, ASAT.related_id), ASAT.invoice_distribution_id) ALLOC_TO_MAIN_DIST_ID ,ASAT.encumbered_flag AID_ENCUMBERED_FLAG ,POD.PO_DISTRIBUTION_ID POD_PO_DISTRIBUTION_ID ,POD.ENCUMBERED_FLAG POD_ENCUMBERED_FLAG ,POD.ENCUMBERED_AMOUNT POD_ENCUMBERED_AMOUNT ,POD.GL_ENCUMBERED_DATE POD_GL_ENCUMBERED_DATE ,POD.GL_Encumbered_Period_Name POD_GL_Encumbered_Period_Name ,POD.ATTRIBUTE_CATEGORY POD_ATTRIBUTE_CATEGORY ,POD.ATTRIBUTE1 POD_ATTRIBUTE1 ,POD.ATTRIBUTE2 POD_ATTRIBUTE2 ,POD.ATTRIBUTE3 POD_ATTRIBUTE3 ,POD.ATTRIBUTE4 POD_ATTRIBUTE4 ,POD.ATTRIBUTE5 POD_ATTRIBUTE5 ,POD.ATTRIBUTE6 POD_ATTRIBUTE6 ,POD.ATTRIBUTE7 POD_ATTRIBUTE7 ,POD.ATTRIBUTE8 POD_ATTRIBUTE8 ,POD.ATTRIBUTE9 POD_ATTRIBUTE9 ,POD.ATTRIBUTE10 POD_ATTRIBUTE10 ,POD.ATTRIBUTE11 POD_ATTRIBUTE11 ,POD.ATTRIBUTE12 POD_ATTRIBUTE12 ,POD.ATTRIBUTE13 POD_ATTRIBUTE13 ,POD.ATTRIBUTE14 POD_ATTRIBUTE14 ,POD.ATTRIBUTE15 POD_ATTRIBUTE15 ,POD.DISTRIBUTION_NUM POD_DISTRIBUTION_NUM ,POD.ACCRUE_ON_RECEIPT_FLAG POD_ACCRUE_ON_RECEIPT_FLAG ,POD.CODE_COMBINATION_ID POD_CCID ,POD.variance_Account_id PO_variance_Account ,POD.destination_type_code Related_Inv_Dist_Dest_Typ ,POD.po_header_id POD_PO_HEADER_ID ,POD.po_line_id POD_PO_LINE_ID ,POD.line_location_id POD_LINE_LOCATION_ID ,AIL.REFERENCE_KEY1 AIL_REFERENCE_KEY1 ,AIL.PRODUCT_TABLE AIL_PRODUCT_TABLE ,'Y' Override_Acctd_Amt_Flag ,zdt.INTERNAL_ORGANIZATION_ID ORG_ID ,zdt.LEDGER_ID LEDGER_ID ,DECODE(RCV.TRANSACTION_ID, NULL, NVL(POD.RATE, POH.RATE), RCV.CURRENCY_CONVERSION_RATE) PO_RCV_CONVERSION_RATE ,DECODE(RCV.TRANSACTION_ID, NULL, NVL(POD.RATE_DATE, POD.CREATION_DATE), NVL(RCV.CURRENCY_CONVERSION_DATE, RCV.TRANSACTION_DATE)) PO_RCV_CONVERSION_DATE ,DECODE(RCV.TRANSACTION_ID, NULL, POH.RATE_TYPE, RCV.CURRENCY_CONVERSION_TYPE) PO_RCV_CONVERSION_RATE_TYPE, AIL.lcm_enabled_flag, ASAT.data_set_id DATA_SET_ID, GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID, GLET1.encumbrance_type_id INVOICE_ENCUMBRANCE_TYPE_ID, CASE WHEN (ASAT.funds_status LIKE 'RESERVED%' AND ASAT.funds_status NOT IN ('RESERVED_NOT_APPLICABLE','NOT_REQUIRED_BY_SUBLEDGER') AND ASAT.LINE_TYPE_LOOKUP_CODE NOT IN ('REC_TAX', 'AWT', 'ERV', 'TERV') ) THEN 'Y' WHEN ASAT.funds_status = 'RESERVED_NOT_APPLICABLE' then CASE WHEN ASAT.PJC_PROJECT_ID is not null and POD.destination_type_code in('INVENTORY','MANUFACTURING') THEN 'Y' ELSE 'N' END WHEN ASAT.funds_status = 'NOT_REQUIRED_BY_SUBLEDGER' THEN 'R' WHEN ASAT.funds_status = 'NOT_ATTEMPTED' then NULL END SUBLEDGER_BC_COMPLETE_STATUS ,ASAT_ERV_REL.dist_code_combination_id VARIANCE_PARENT_DIST_CCID ,-99 BACKING_BURDEN_DISTRIBUTION_ID ,(select item.dist_code_combination_id from ap_invoice_distributions_all item Where item.invoice_distribution_id = asat.charge_applicable_to_dist_id) AID_CHARGE_TO_ITEM_CCID , cast(ail.creation_date as DATE) ail_creation_date , ail.created_by ail_created_by , cast(ail.last_update_date as DATE) ail_last_update_date , ail.last_updated_by ail_last_updated_by ,AIL.ACCOUNTING_DATE AIL_ACCOUNTING_DATE ,AIL.ASSETS_TRACKING_FLAG AIL_ASSETS_TRACKING_FLAG ,AIL.INCOME_TAX_REGION AIL_INCOME_TAX_REGION ,AIL.TYPE_1099 AIL_TYPE_1099 ,AIL.STAT_AMOUNT AIL_STAT_AMOUNT ,AIL.ATTRIBUTE_NUMBER1 AIL_ATTRIBUTE_NUMBER1 ,AIL.ATTRIBUTE_NUMBER2 AIL_ATTRIBUTE_NUMBER2 ,AIL.ATTRIBUTE_NUMBER3 AIL_ATTRIBUTE_NUMBER3 ,AIL.ATTRIBUTE_NUMBER4 AIL_ATTRIBUTE_NUMBER4 ,AIL.ATTRIBUTE_NUMBER5 AIL_ATTRIBUTE_NUMBER5 ,AIL.ATTRIBUTE_DATE1 AIL_ATTRIBUTE_DATE1 ,AIL.ATTRIBUTE_DATE2 AIL_ATTRIBUTE_DATE2 ,AIL.ATTRIBUTE_DATE3 AIL_ATTRIBUTE_DATE3 ,AIL.ATTRIBUTE_DATE4 AIL_ATTRIBUTE_DATE4 ,AIL.ATTRIBUTE_DATE5 AIL_ATTRIBUTE_DATE5 ,AIL.GLOBAL_ATTRIBUTE_NUMBER1 AIL_GLOBAL_ATTRIBUTE_NUM1 ,AIL.GLOBAL_ATTRIBUTE_NUMBER2 AIL_GLOBAL_ATTRIBUTE_NUM2 ,AIL.GLOBAL_ATTRIBUTE_NUMBER3 AIL_GLOBAL_ATTRIBUTE_NUM3 ,AIL.GLOBAL_ATTRIBUTE_NUMBER4 AIL_GLOBAL_ATTRIBUTE_NUM4 ,AIL.GLOBAL_ATTRIBUTE_NUMBER5 AIL_GLOBAL_ATTRIBUTE_NUM5 ,AIL.GLOBAL_ATTRIBUTE_DATE1 AIL_GLOBAL_ATTRIBUTE_DATE1 ,AIL.GLOBAL_ATTRIBUTE_DATE2 AIL_GLOBAL_ATTRIBUTE_DATE2 ,AIL.GLOBAL_ATTRIBUTE_DATE3 AIL_GLOBAL_ATTRIBUTE_DATE3 ,AIL.GLOBAL_ATTRIBUTE_DATE4 AIL_GLOBAL_ATTRIBUTE_DATE4 ,AIL.GLOBAL_ATTRIBUTE_DATE5 AIL_GLOBAL_ATTRIBUTE_DATE5 FROM AP_INVOICE_LINES_ALL AIL, AP_SELF_ASSESSED_TAX_DIST_ALL ASAT, AP_SELF_ASSESSED_TAX_DIST_ALL ASAT_ERV_REL, ZX_CONDITION_GROUPS_TL ZCG, PO_DISTRIBUTIONS_ALL POD, zx_rec_nrec_dist zdt, zx_accounts accounts, RCV_TRANSACTIONS RCV, PO_HEADERS_ALL POH, GL_ENCUMBRANCE_TYPES_B GLET, GL_ENCUMBRANCE_TYPES_B GLET1 WHERE ASAT.invoice_line_number = AIL.line_number AND ASAT.invoice_id = AIL.invoice_id AND ASAT.line_type_lookup_code <> 'PREPAY' AND ASAT.awt_group_id = ZCG.condition_group_id(+) AND userenv('LANG') = ZCG.language(+) AND ASAT.po_distribution_id = POD.po_distribution_id(+) AND ASAT.detail_tax_dist_id = zdt.REC_NREC_TAX_DIST_ID(+) AND accounts.TAX_ACCOUNT_ENTITY_ID (+) = nvl(ZDT.ACCOUNT_SOURCE_TAX_RATE_ID, ZDT.TAX_RATE_ID) AND accounts.TAX_ACCOUNT_ENTITY_CODE (+)= 'RATES' AND accounts.INTERNAL_ORGANIZATION_ID (+)= ZDT.INTERNAL_ORGANIZATION_ID AND accounts.LEDGER_ID (+) = ZDT.LEDGER_ID AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+) AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+) /* bug 14104062 - start */ AND ASAT_ERV_REL.invoice_id(+) = ASAT.invoice_id AND ASAT_ERV_REL.invoice_distribution_id(+) = ASAT.related_id /* bug 14104062 - end */ AND GLET.encumbrance_type_code='Obligation' AND GLET.enabled_flag='Y' AND GLET1.encumbrance_type_code = 'ORA_Invoice' AND GLET1.enabled_flag = 'Y'