DBA Data[Home] [Help]

VIEW: APPS.AP_INVOICE_EXTRACT_DETAILS_V

Source

View Text - Preformatted

SELECT  AID.accounting_event_id
               ,AID.invoice_distribution_id
               ,AIL.deferred_acctg_flag
               ,AIL.def_acctg_start_date
               ,AIL.def_acctg_end_date
               ,AIL.def_acctg_period_type
               ,AIL.def_acctg_number_of_periods
               ,AID.invoice_distribution_id
               ,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'))
               ,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_code_combination_id
               ,AID.distribution_line_number
               ,AID.expenditure_item_date
               ,AID.expenditure_type
               ,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.type_1099
               ,AWTG.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.SUMMARY_TAX_LINE_ID
               ,AID.assets_addition_flag
               ,AID.line_type_lookup_code
               ,AID.rate_var_code_combination_id
               ,AID.reversal_flag
               ,AID.global_attribute_category
               ,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_distribution_id
               ,AID.parent_reversal_id
               ,AID.awt_flag
               ,-1
               ,AID.detail_tax_dist_id
               ,'N'
               ,-1
               ,AWT_RELATED_IDS.dist_code_combination_id AWT_Related_dist_ccid
               ,nvl(AIL.base_Amount,AIL.amount)
               ,AIL.amount
               ,NVL(AID.rounding_amt, 0)
               ,nvl(AID.Base_Amount, AID.amount)
               ,(nvl(AID.Base_Amount, AID.amount)
                      - nvl(AID.rounding_amt,0))
               ,'AP_INV_DIST'
               ,AID.po_distribution_id
               ,200
               ,'AP_INV_DIST'
               ,'AP_INVOICES'
               ,AID.invoice_distribution_Id
               ,AID.invoice_id
               ,201
               ,'PO_DISTRIBUTIONS_ALL'
               ,Decode (pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE')
               ,AID.po_distribution_id
               ,Decode (pod.po_release_id, NULL, pod.po_header_id,pod.po_release_id)
               ,AID.project_id
               ,AID.task_id
               ,AID.Expenditure_Organization_ID
               ,AID.quantity_variance
               ,nvl(AID.base_quantity_variance,AID.quantity_variance)
               ,AID.amount_variance
               ,nvl(AID.base_amount_variance,AID.amount_variance)
	             ,decode(AID.line_type_lookup_code, 'RETAINAGE',
                       AIDR.dist_code_combination_id, null)
	             ,decode(AIL.line_type_lookup_code, 'RETAINAGE RELEASE',
                       AIL.retained_invoice_id, null)
	             ,decode(AIL.line_type_lookup_code, 'RETAINAGE RELEASE',
                       AID.retained_invoice_dist_id, null)
	             ,NVL(AID.final_release_rounding, 0)
               ,AIL.unit_price
               ,AIL.quantity_invoiced
               ,AID.unit_price
               ,AID.quantity_invoiced
               ,AID.parent_invoice_id
               ,AID.final_match_flag
               ,NVL(AID.EXTRA_PO_ERV,0)
               ,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))
               ,NVL(AID.amount,0)  - NVL(AID.amount_variance,0)
                - NVL(AID.quantity_variance,0)
               ,AID.related_id
               ,AID.CHARGE_APPLICABLE_TO_DIST_ID
               ,NVL(NVL(AID.charge_applicable_to_dist_id,
                        AID.related_id),
                    AID.invoice_distribution_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.CODE_COMBINATION_ID
               ,POD.variance_Account_id
               ,POD.destination_type_code
               ,POD.po_header_id
               ,POD.po_line_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(
                  201,
	          'AP_INVOICES',
	          aid.invoice_id,
	          'AP_INV_DIST',
	          aid.parent_reversal_id)
                 ELSE   'N'  END "UPG_AP_ENCUM_OPTION"
                ,GLET.encumbrance_type_id
                ,AID.dist_code_combination_id
                ,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))
                ,NVL(AID.amount,0)  - NVL(AID.amount_variance,0)
                  - NVL(AID.quantity_variance,0)
                ,GLET.encumbrance_type_id
                ,AID.dist_code_combination_id
                ,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))
                ,NVL(AID.amount,0)  - NVL(AID.amount_variance,0)
                  - NVL(AID.quantity_variance,0)
                ,AIL.REFERENCE_KEY1
               ,AIL.PRODUCT_TABLE
	       ,'Y' Override_Acctd_Amt_Flag
	   FROM  AP_INVOICE_LINES_ALL AIL,
                AP_INVOICE_DISTRIBUTIONS_ALL AID,
                AP_INVOICE_DISTRIBUTIONS_ALL AIDR,
                AP_AWT_GROUPS AWTG,
                AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS,
                PO_DISTRIBUTIONS_ALL POD,
		gl_encumbrance_types GLET,
                FINANCIALS_SYSTEM_PARAMS_ALL FSP
           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 = AWTG.group_id(+)
           AND    AID.awt_related_id = AWT_RELATED_IDS.invoice_distribution_id(+)
           AND    AID.related_retainage_dist_id = AIDR.invoice_distribution_id(+)
           AND    nvl(AID.posted_flag,'N') <> 'Y'
           AND    AID.po_distribution_id = POD.po_distribution_id(+)
	   AND    GLET.encumbrance_type_key='Invoices'
           AND    aid.org_id = fsp.org_id
        UNION all
        SELECT  ASAT.accounting_event_id
               ,ASAT.invoice_distribution_id
               ,AIL.deferred_acctg_flag
               ,AIL.def_acctg_start_date
               ,AIL.def_acctg_end_date
               ,AIL.def_acctg_period_type
               ,AIL.def_acctg_number_of_periods
               ,ASAT.invoice_distribution_id
               ,decode(nvl(ASAT.cancellation_flag,'N')
			   			, 'Y', decode(nvl(ASAT.Parent_Reversal_id,-99), -99,'N','Y')
					    , 'N')
               ,ASAT.accounting_date
               ,ASAT.amount
               ,ASAT.assets_tracking_flag
               ,ASAT.attribute_category
               ,ASAT.attribute1
               ,ASAT.attribute10
               ,ASAT.attribute11
               ,ASAT.attribute12
               ,ASAT.attribute13
               ,ASAT.attribute14
               ,ASAT.attribute15
               ,ASAT.attribute2
               ,ASAT.attribute3
               ,ASAT.attribute4
               ,ASAT.attribute5
               ,ASAT.attribute6
               ,ASAT.attribute7
               ,ASAT.attribute8
               ,ASAT.attribute9
               ,ASAT.award_id
               ,ASAT.description
               ,ASAT.dist_code_combination_id
               ,ASAT.distribution_line_number
               ,ASAT.expenditure_item_date
               ,ASAT.expenditure_type
               ,ASAT.income_tax_region
               ,ASAT.merchant_document_number
               ,ASAT.merchant_reference
               ,ASAT.merchant_tax_reg_number
               ,ASAT.merchant_taxpayer_id
               ,ASAT.period_name
               ,ASAT.reference_1
               ,ASAT.reference_2
               ,ASAT.stat_amount
               ,ASAT.tax_recoverable_flag
               ,ASAT.type_1099
               ,AWTG.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.SUMMARY_TAX_LINE_ID
               ,ASAT.assets_addition_flag
               ,ASAT.line_type_lookup_code
               ,-1
               ,ASAT.reversal_flag
               ,ASAT.global_attribute_category
               ,ASAT.global_attribute1
               ,ASAT.global_attribute2
               ,ASAT.global_attribute3
               ,ASAT.global_attribute4
               ,ASAT.global_attribute5
               ,ASAT.global_attribute6
               ,ASAT.global_attribute7
               ,ASAT.global_attribute8
               ,ASAT.global_attribute9
               ,ASAT.global_attribute10
               ,ASAT.global_attribute11
               ,ASAT.global_attribute12
               ,ASAT.global_attribute13
               ,ASAT.global_attribute14
               ,ASAT.global_attribute15
               ,ASAT.global_attribute16
               ,ASAT.global_attribute17
               ,ASAT.global_attribute18
               ,ASAT.global_attribute19
               ,ASAT.global_attribute20
               ,ASAT.cancellation_flag
               ,ASAT.invoice_line_number
               ,ASAT.invoice_distribution_id
               ,ASAT.parent_reversal_id
               ,ASAT.awt_flag
               ,ASAT.SELF_ASSESSED_TAX_LIAB_CCID
               ,ASAT.detail_tax_dist_id
               ,ASAT.SELF_ASSESSED_FLAG
               ,ASAT.DIST_CODE_COMBINATION_ID
               ,-1
               ,nvl(AIL.base_Amount,AIL.amount)
               ,AIL.amount
               ,NVL(ASAT.rounding_amt, 0)
               ,nvl(ASAT.Base_Amount, ASAT.amount)
               ,(nvl(ASAT.Base_Amount, ASAT.amount)
                      - nvl(ASAT.rounding_amt,0))
               ,'AP_INV_DIST'
               ,ASAT.po_distribution_id
               ,200
               ,'AP_INV_DIST'
               ,'AP_INVOICES'
               ,ASAT.invoice_distribution_Id
               ,ASAT.invoice_id
               ,201
               ,'PO_DISTRIBUTIONS_ALL'
               ,Decode (pod.po_release_id,NULL,'PURCHASE_ORDER', 'RELEASE')
               ,ASAT.po_distribution_id
               ,Decode (pod.po_release_id,NULL,pod.po_header_id,pod.po_release_id)
               ,ASAT.project_id
               ,ASAT.task_id
               ,ASAT.Expenditure_Organization_ID
               ,ASAT.quantity_variance
               ,nvl(ASAT.base_quantity_variance, ASAT.quantity_variance)
               ,ASAT.amount_variance
               ,nvl(ASAT.base_amount_variance, ASAT.amount_variance)
	             ,-1
	             ,-1
	             ,-1
	             ,0
               ,AIL.unit_price
               ,AIL.quantity_invoiced
               ,ASAT.unit_price
               ,ASAT.quantity_invoiced
               ,ASAT.parent_invoice_id
               ,ASAT.final_match_flag
               ,NVL(ASAT.EXTRA_PO_ERV,0)
               ,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))
               ,NVL(ASAT.amount,0)  - NVL(ASAT.amount_variance,0)
                - NVL(ASAT.quantity_variance,0)
               ,ASAT.related_id
               ,ASAT.CHARGE_APPLICABLE_TO_DIST_ID
               ,NVL(NVL(ASAT.charge_applicable_to_dist_id,
                        ASAT.related_id),
                    ASAT.invoice_distribution_id)
               ,ASAT.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.CODE_COMBINATION_ID
               ,POD.variance_Account_id
               ,POD.destination_type_code
               ,POD.po_header_id
               ,POD.po_line_id
               ,POD.line_location_id
               ,'N'
               ,NULL
               ,NULL
               ,NULL
               ,NULL
               ,NULL
               ,NULL
               ,NULL
               ,NULL
               ,AIL.REFERENCE_KEY1
               ,AIL.PRODUCT_TABLE
	       ,'Y' Override_Acctd_Amt_Flag
	  FROM  AP_INVOICE_LINES_ALL AIL,
                AP_SELF_ASSESSED_TAX_DIST_ALL ASAT,
                AP_AWT_GROUPS AWTG,
                PO_DISTRIBUTIONS_ALL POD
           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 = AWTG.group_id(+)
           AND    nvl(ASAT.posted_flag,'N') <> 'Y'
           AND    ASAT.po_distribution_id = POD.po_distribution_id(+)

View Text - HTML Formatted

SELECT AID.ACCOUNTING_EVENT_ID
, AID.INVOICE_DISTRIBUTION_ID
, AIL.DEFERRED_ACCTG_FLAG
, AIL.DEF_ACCTG_START_DATE
, AIL.DEF_ACCTG_END_DATE
, AIL.DEF_ACCTG_PERIOD_TYPE
, AIL.DEF_ACCTG_NUMBER_OF_PERIODS
, AID.INVOICE_DISTRIBUTION_ID
, 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'))
, 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_CODE_COMBINATION_ID
, AID.DISTRIBUTION_LINE_NUMBER
, AID.EXPENDITURE_ITEM_DATE
, AID.EXPENDITURE_TYPE
, 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.TYPE_1099
, AWTG.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.SUMMARY_TAX_LINE_ID
, AID.ASSETS_ADDITION_FLAG
, AID.LINE_TYPE_LOOKUP_CODE
, AID.RATE_VAR_CODE_COMBINATION_ID
, AID.REVERSAL_FLAG
, AID.GLOBAL_ATTRIBUTE_CATEGORY
, 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_DISTRIBUTION_ID
, AID.PARENT_REVERSAL_ID
, AID.AWT_FLAG
, -1
, AID.DETAIL_TAX_DIST_ID
, 'N'
, -1
, AWT_RELATED_IDS.DIST_CODE_COMBINATION_ID AWT_RELATED_DIST_CCID
, NVL(AIL.BASE_AMOUNT
, AIL.AMOUNT)
, AIL.AMOUNT
, NVL(AID.ROUNDING_AMT
, 0)
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT)
, (NVL(AID.BASE_AMOUNT
, AID.AMOUNT) - NVL(AID.ROUNDING_AMT
, 0))
, 'AP_INV_DIST'
, AID.PO_DISTRIBUTION_ID
, 200
, 'AP_INV_DIST'
, 'AP_INVOICES'
, AID.INVOICE_DISTRIBUTION_ID
, AID.INVOICE_ID
, 201
, 'PO_DISTRIBUTIONS_ALL'
, DECODE (POD.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE')
, AID.PO_DISTRIBUTION_ID
, DECODE (POD.PO_RELEASE_ID
, NULL
, POD.PO_HEADER_ID
, POD.PO_RELEASE_ID)
, AID.PROJECT_ID
, AID.TASK_ID
, AID.EXPENDITURE_ORGANIZATION_ID
, AID.QUANTITY_VARIANCE
, NVL(AID.BASE_QUANTITY_VARIANCE
, AID.QUANTITY_VARIANCE)
, AID.AMOUNT_VARIANCE
, NVL(AID.BASE_AMOUNT_VARIANCE
, AID.AMOUNT_VARIANCE)
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'RETAINAGE'
, AIDR.DIST_CODE_COMBINATION_ID
, NULL)
, DECODE(AIL.LINE_TYPE_LOOKUP_CODE
, 'RETAINAGE RELEASE'
, AIL.RETAINED_INVOICE_ID
, NULL)
, DECODE(AIL.LINE_TYPE_LOOKUP_CODE
, 'RETAINAGE RELEASE'
, AID.RETAINED_INVOICE_DIST_ID
, NULL)
, NVL(AID.FINAL_RELEASE_ROUNDING
, 0)
, AIL.UNIT_PRICE
, AIL.QUANTITY_INVOICED
, AID.UNIT_PRICE
, AID.QUANTITY_INVOICED
, AID.PARENT_INVOICE_ID
, AID.FINAL_MATCH_FLAG
, NVL(AID.EXTRA_PO_ERV
, 0)
, 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))
, NVL(AID.AMOUNT
, 0) - NVL(AID.AMOUNT_VARIANCE
, 0) - NVL(AID.QUANTITY_VARIANCE
, 0)
, AID.RELATED_ID
, AID.CHARGE_APPLICABLE_TO_DIST_ID
, NVL(NVL(AID.CHARGE_APPLICABLE_TO_DIST_ID
, AID.RELATED_ID)
, AID.INVOICE_DISTRIBUTION_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.CODE_COMBINATION_ID
, POD.VARIANCE_ACCOUNT_ID
, POD.DESTINATION_TYPE_CODE
, POD.PO_HEADER_ID
, POD.PO_LINE_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( 201
, 'AP_INVOICES'
, AID.INVOICE_ID
, 'AP_INV_DIST'
, AID.PARENT_REVERSAL_ID) ELSE 'N' END "UPG_AP_ENCUM_OPTION"
, GLET.ENCUMBRANCE_TYPE_ID
, AID.DIST_CODE_COMBINATION_ID
, 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))
, NVL(AID.AMOUNT
, 0) - NVL(AID.AMOUNT_VARIANCE
, 0) - NVL(AID.QUANTITY_VARIANCE
, 0)
, GLET.ENCUMBRANCE_TYPE_ID
, AID.DIST_CODE_COMBINATION_ID
, 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))
, NVL(AID.AMOUNT
, 0) - NVL(AID.AMOUNT_VARIANCE
, 0) - NVL(AID.QUANTITY_VARIANCE
, 0)
, AIL.REFERENCE_KEY1
, AIL.PRODUCT_TABLE
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
FROM AP_INVOICE_LINES_ALL AIL
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICE_DISTRIBUTIONS_ALL AIDR
, AP_AWT_GROUPS AWTG
, AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS
, PO_DISTRIBUTIONS_ALL POD
, GL_ENCUMBRANCE_TYPES GLET
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
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 = AWTG.GROUP_ID(+)
AND AID.AWT_RELATED_ID = AWT_RELATED_IDS.INVOICE_DISTRIBUTION_ID(+)
AND AID.RELATED_RETAINAGE_DIST_ID = AIDR.INVOICE_DISTRIBUTION_ID(+)
AND NVL(AID.POSTED_FLAG
, 'N') <> 'Y'
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND GLET.ENCUMBRANCE_TYPE_KEY='INVOICES'
AND AID.ORG_ID = FSP.ORG_ID UNION ALL SELECT ASAT.ACCOUNTING_EVENT_ID
, ASAT.INVOICE_DISTRIBUTION_ID
, AIL.DEFERRED_ACCTG_FLAG
, AIL.DEF_ACCTG_START_DATE
, AIL.DEF_ACCTG_END_DATE
, AIL.DEF_ACCTG_PERIOD_TYPE
, AIL.DEF_ACCTG_NUMBER_OF_PERIODS
, ASAT.INVOICE_DISTRIBUTION_ID
, DECODE(NVL(ASAT.CANCELLATION_FLAG
, 'N')
, 'Y'
, DECODE(NVL(ASAT.PARENT_REVERSAL_ID
, -99)
, -99
, 'N'
, 'Y')
, 'N')
, ASAT.ACCOUNTING_DATE
, ASAT.AMOUNT
, ASAT.ASSETS_TRACKING_FLAG
, ASAT.ATTRIBUTE_CATEGORY
, ASAT.ATTRIBUTE1
, ASAT.ATTRIBUTE10
, ASAT.ATTRIBUTE11
, ASAT.ATTRIBUTE12
, ASAT.ATTRIBUTE13
, ASAT.ATTRIBUTE14
, ASAT.ATTRIBUTE15
, ASAT.ATTRIBUTE2
, ASAT.ATTRIBUTE3
, ASAT.ATTRIBUTE4
, ASAT.ATTRIBUTE5
, ASAT.ATTRIBUTE6
, ASAT.ATTRIBUTE7
, ASAT.ATTRIBUTE8
, ASAT.ATTRIBUTE9
, ASAT.AWARD_ID
, ASAT.DESCRIPTION
, ASAT.DIST_CODE_COMBINATION_ID
, ASAT.DISTRIBUTION_LINE_NUMBER
, ASAT.EXPENDITURE_ITEM_DATE
, ASAT.EXPENDITURE_TYPE
, ASAT.INCOME_TAX_REGION
, ASAT.MERCHANT_DOCUMENT_NUMBER
, ASAT.MERCHANT_REFERENCE
, ASAT.MERCHANT_TAX_REG_NUMBER
, ASAT.MERCHANT_TAXPAYER_ID
, ASAT.PERIOD_NAME
, ASAT.REFERENCE_1
, ASAT.REFERENCE_2
, ASAT.STAT_AMOUNT
, ASAT.TAX_RECOVERABLE_FLAG
, ASAT.TYPE_1099
, AWTG.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.SUMMARY_TAX_LINE_ID
, ASAT.ASSETS_ADDITION_FLAG
, ASAT.LINE_TYPE_LOOKUP_CODE
, -1
, ASAT.REVERSAL_FLAG
, ASAT.GLOBAL_ATTRIBUTE_CATEGORY
, ASAT.GLOBAL_ATTRIBUTE1
, ASAT.GLOBAL_ATTRIBUTE2
, ASAT.GLOBAL_ATTRIBUTE3
, ASAT.GLOBAL_ATTRIBUTE4
, ASAT.GLOBAL_ATTRIBUTE5
, ASAT.GLOBAL_ATTRIBUTE6
, ASAT.GLOBAL_ATTRIBUTE7
, ASAT.GLOBAL_ATTRIBUTE8
, ASAT.GLOBAL_ATTRIBUTE9
, ASAT.GLOBAL_ATTRIBUTE10
, ASAT.GLOBAL_ATTRIBUTE11
, ASAT.GLOBAL_ATTRIBUTE12
, ASAT.GLOBAL_ATTRIBUTE13
, ASAT.GLOBAL_ATTRIBUTE14
, ASAT.GLOBAL_ATTRIBUTE15
, ASAT.GLOBAL_ATTRIBUTE16
, ASAT.GLOBAL_ATTRIBUTE17
, ASAT.GLOBAL_ATTRIBUTE18
, ASAT.GLOBAL_ATTRIBUTE19
, ASAT.GLOBAL_ATTRIBUTE20
, ASAT.CANCELLATION_FLAG
, ASAT.INVOICE_LINE_NUMBER
, ASAT.INVOICE_DISTRIBUTION_ID
, ASAT.PARENT_REVERSAL_ID
, ASAT.AWT_FLAG
, ASAT.SELF_ASSESSED_TAX_LIAB_CCID
, ASAT.DETAIL_TAX_DIST_ID
, ASAT.SELF_ASSESSED_FLAG
, ASAT.DIST_CODE_COMBINATION_ID
, -1
, NVL(AIL.BASE_AMOUNT
, AIL.AMOUNT)
, AIL.AMOUNT
, NVL(ASAT.ROUNDING_AMT
, 0)
, NVL(ASAT.BASE_AMOUNT
, ASAT.AMOUNT)
, (NVL(ASAT.BASE_AMOUNT
, ASAT.AMOUNT) - NVL(ASAT.ROUNDING_AMT
, 0))
, 'AP_INV_DIST'
, ASAT.PO_DISTRIBUTION_ID
, 200
, 'AP_INV_DIST'
, 'AP_INVOICES'
, ASAT.INVOICE_DISTRIBUTION_ID
, ASAT.INVOICE_ID
, 201
, 'PO_DISTRIBUTIONS_ALL'
, DECODE (POD.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE')
, ASAT.PO_DISTRIBUTION_ID
, DECODE (POD.PO_RELEASE_ID
, NULL
, POD.PO_HEADER_ID
, POD.PO_RELEASE_ID)
, ASAT.PROJECT_ID
, ASAT.TASK_ID
, ASAT.EXPENDITURE_ORGANIZATION_ID
, ASAT.QUANTITY_VARIANCE
, NVL(ASAT.BASE_QUANTITY_VARIANCE
, ASAT.QUANTITY_VARIANCE)
, ASAT.AMOUNT_VARIANCE
, NVL(ASAT.BASE_AMOUNT_VARIANCE
, ASAT.AMOUNT_VARIANCE)
, -1
, -1
, -1
, 0
, AIL.UNIT_PRICE
, AIL.QUANTITY_INVOICED
, ASAT.UNIT_PRICE
, ASAT.QUANTITY_INVOICED
, ASAT.PARENT_INVOICE_ID
, ASAT.FINAL_MATCH_FLAG
, NVL(ASAT.EXTRA_PO_ERV
, 0)
, 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))
, NVL(ASAT.AMOUNT
, 0) - NVL(ASAT.AMOUNT_VARIANCE
, 0) - NVL(ASAT.QUANTITY_VARIANCE
, 0)
, ASAT.RELATED_ID
, ASAT.CHARGE_APPLICABLE_TO_DIST_ID
, NVL(NVL(ASAT.CHARGE_APPLICABLE_TO_DIST_ID
, ASAT.RELATED_ID)
, ASAT.INVOICE_DISTRIBUTION_ID)
, ASAT.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.CODE_COMBINATION_ID
, POD.VARIANCE_ACCOUNT_ID
, POD.DESTINATION_TYPE_CODE
, POD.PO_HEADER_ID
, POD.PO_LINE_ID
, POD.LINE_LOCATION_ID
, 'N'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, AIL.REFERENCE_KEY1
, AIL.PRODUCT_TABLE
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
FROM AP_INVOICE_LINES_ALL AIL
, AP_SELF_ASSESSED_TAX_DIST_ALL ASAT
, AP_AWT_GROUPS AWTG
, PO_DISTRIBUTIONS_ALL POD
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 = AWTG.GROUP_ID(+)
AND NVL(ASAT.POSTED_FLAG
, 'N') <> 'Y'
AND ASAT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)