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(+)
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(+)
|
|
|