[Home] [Help]
SELECT AID.bc_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
,'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
,Decode (IGC.CC_LINE, null, 201, IGC.IGC_APPLICATION_ID)
,Decode (IGC.CC_LINE, null, 'PO_DISTRIBUTIONS_ALL', IGC.CC_DIST_TYPE)
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE') , IGC.CC_ENTITY_CODE)
,Decode (IGC.CC_LINE, null, AID.po_distribution_id, igc.cc_dist_id)
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, pod.po_header_id,pod.po_release_id)
,IGC.cc_doc_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)
,-1
,-1
,-1
,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 (Decode(nvl(AID.parent_reversal_id, -99), -99,
Decode(nvl(AID.po_distribution_id,-99), -99, 200, 201),200),
Decode(nvl(AID.parent_reversal_id, -99), -99,
Decode(nvl(AID.po_distribution_id,-99), -99, 'AP_INVOICES',
Decode(pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE')),'AP_INVOICES'),
Decode(nvl(AID.parent_reversal_id, -99), -99,
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)),AID.invoice_id),
Decode(nvl(AID.parent_reversal_id, -99), -99,
Decode(nvl(AID.po_distribution_id,-99), -99, 'AP_INV_DIST', 'PO_DISTRIBUTIONS_ALL'),'AP_INV_DIST'),
Decode(nvl(AID.parent_reversal_id, -99), -99,
Decode(nvl(AID.po_distribution_id,-99), -99, AID.parent_reversal_id, AID.po_distribution_id), AID.parent_reversal_id )) ELSE 'N' END "UPG_AP_ENCUM_OPTION"
,Decode(nvl(AID.po_distribution_id,-99), -99, NULL, (SELECT encumbrance_type_id FROM GL_ENCUMBRANCE_TYPES
WHERE encumbrance_type_key = 'Obligation'
and enabled_flag='Y'))
,Decode(nvl(AID.po_distribution_id,-99), -99, NULL, POD.CODE_COMBINATION_ID)
/*,Decode(nvl(AID.po_distribution_id,-99), -99, NULL,
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)) )*/
,AP_UTILITIES_PKG.ap_round_currency (
Decode(nvl(AID.po_distribution_id,-99), -99,
(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)) * nvl(POD.rate,1))),
asp.BASE_CURRENCY_CODE) /*bug7570932*/
,Decode(nvl(AID.po_distribution_id,-99), -99, NULL,
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)
,decode(AID.line_type_lookup_code , 'ERV' , 'Y' , 'N') Override_Acctd_Amt_Flag
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,AI.EXCHANGE_RATE ,NVL(POD.RATE, POH.RATE)),RCV.CURRENCY_CONVERSION_RATE) Po_Rcv_Inv_Curr_Conv_Rate
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,nvl(AI.EXCHANGE_DATE,AI.GL_DATE) ,NVL(POD.RATE_DATE,POH.CREATION_DATE)),
NVL(RCV.CURRENCY_CONVERSION_DATE,RCV.TRANSACTION_DATE)) Po_Rcv_Inv_Curr_Conv_Date
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,AI.EXCHANGE_RATE_TYPE ,POH.RATE_TYPE),RCV.CURRENCY_CONVERSION_TYPE) Po_Rcv_Inv_Curr_Conv_Type
,AID_ERV_REL.dist_code_combination_id VARIANCE_PARENT_DIST_CCID--bug13508145
,DECODE(RCV.LCM_SHIPMENT_LINE_ID,NULL,'No','Yes') LCM_ENABLED
,RCV.ORGANIZATION_ID
FROM AP_INVOICE_LINES_ALL AIL,
AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICE_DISTRIBUTIONS_ALL AID_ERV_REL, --bug13508145
AP_AWT_GROUPS AWTG,
AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS,
PO_DISTRIBUTIONS_ALL POD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
gl_encumbrance_types GLET,
IGC_CC_SLA_BFLOW_PRIOR_V IGC,
RCV_TRANSACTIONS RCV,
PO_HEADERS_ALL POH,
ap_system_parameters_all ASP /*BUG7570932*/
WHERE AID.invoice_line_number = AIL.line_number
AND AID.invoice_id = AIL.invoice_id
AND AID.invoice_id = AI.invoice_id
AND AID.AWT_group_id = AWTG.group_id(+)
AND AID.awt_related_id = AWT_RELATED_IDS.invoice_distribution_id(+)
AND nvl(AID.posted_flag,'N') <> 'Y'
AND AID.po_distribution_id = POD.po_distribution_id(+)
AND AID.po_distribution_id = IGC.po_distribution_id(+)
AND AID.ORG_ID = FSP.ORG_ID
AND GLET.encumbrance_type_key='Invoices'
AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+)
AND aid.set_of_books_id = asp.set_of_books_id
AND aid.org_id = asp.org_id /*BUG7570932*/
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
/* bug13508145 - start */
AND AID_ERV_REL.invoice_id(+) = AID.invoice_id
AND AID_ERV_REL.invoice_distribution_id(+) = AID.related_id
/* bug13508145 - end */
UNION all
SELECT ASAT.bc_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(ASAT.Parent_Reversal_id,
null,'N','Y'),'N') */
,'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
,Decode (IGC.CC_LINE, null, 201, IGC.IGC_APPLICATION_ID)
,Decode (IGC.CC_LINE, null, 'PO_DISTRIBUTIONS_ALL', IGC.CC_DIST_TYPE)
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE'), IGC.CC_ENTITY_CODE)
,Decode (IGC.CC_LINE, null, ASAT.po_distribution_id, igc.cc_dist_id)
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, pod.po_header_id,pod.po_release_id),IGC.cc_doc_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
,decode(AIL.line_type_lookup_code , 'ERV' , 'Y' , 'N') Override_Acctd_Amt_Flag
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,AI.EXCHANGE_RATE ,NVL(POD.RATE, POH.RATE)),RCV.CURRENCY_CONVERSION_RATE) Po_Rcv_Inv_Curr_Conv_Rate
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,NVL(AI.EXCHANGE_DATE,AI.GL_DATE) ,NVL(POD.RATE_DATE,POH.CREATION_DATE)),
NVL(RCV.CURRENCY_CONVERSION_DATE,RCV.TRANSACTION_DATE)) Po_Rcv_Inv_Curr_Conv_Date
,DECODE(RCV.TRANSACTION_ID, NULL, DECODE(POD.PO_DISTRIBUTION_ID,
NULL,AI.EXCHANGE_RATE_TYPE ,POH.RATE_TYPE),RCV.CURRENCY_CONVERSION_TYPE) P_Rcv_Inv_Curr_Conv_Type
,ASAT_ERV_REL.dist_code_combination_id VARIANCE_PARENT_DIST_CCID--bug13508145
,DECODE(RCV.LCM_SHIPMENT_LINE_ID,NULL,'No','Yes') LCM_ENABLED
,RCV.ORGANIZATION_ID
FROM AP_INVOICE_LINES_ALL AIL,
AP_INVOICES_ALL AI,
AP_SELF_ASSESSED_TAX_DIST_ALL ASAT,
AP_SELF_ASSESSED_TAX_DIST_ALL ASAT_ERV_REL, --bug13508145
AP_AWT_GROUPS AWTG,
PO_DISTRIBUTIONS_ALL POD,
RCV_TRANSACTIONS RCV,
PO_HEADERS_ALL POH,
IGC_CC_SLA_BFLOW_PRIOR_V IGC
WHERE ASAT.invoice_line_number = AIL.line_number
AND ASAT.invoice_id = AIL.invoice_id
AND ASAT.invoice_id = AI.invoice_id
AND ASAT.AWT_group_id = AWTG.group_id(+)
AND nvl(ASAT.posted_flag,'N') <> 'Y'
AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+)
AND ASAT.po_distribution_id = POD.po_distribution_id(+)
AND ASAT.po_distribution_id = IGC.po_distribution_id(+)
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
/* bug13508145 - start */
AND ASAT_ERV_REL.invoice_id(+) = ASAT.invoice_id
AND ASAT_ERV_REL.invoice_distribution_id(+) = ASAT.related_id
/* bug13508145 - end */
SELECT AID.BC_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
, '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
, DECODE (IGC.CC_LINE
, NULL
, 201
, IGC.IGC_APPLICATION_ID)
, DECODE (IGC.CC_LINE
, NULL
, 'PO_DISTRIBUTIONS_ALL'
, IGC.CC_DIST_TYPE)
, DECODE (IGC.CC_LINE
, NULL
, DECODE (POD.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE')
, IGC.CC_ENTITY_CODE)
, DECODE (IGC.CC_LINE
, NULL
, AID.PO_DISTRIBUTION_ID
, IGC.CC_DIST_ID)
, DECODE (IGC.CC_LINE
, NULL
, DECODE (POD.PO_RELEASE_ID
, NULL
, POD.PO_HEADER_ID
, POD.PO_RELEASE_ID)
, IGC.CC_DOC_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)
, -1
, -1
, -1
, 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 (DECODE(NVL(AID.PARENT_REVERSAL_ID
, -99)
, -99
,
DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 200
, 201)
, 200)
,
DECODE(NVL(AID.PARENT_REVERSAL_ID
, -99)
, -99
,
DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 'AP_INVOICES'
,
DECODE(POD.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE'))
, 'AP_INVOICES')
,
DECODE(NVL(AID.PARENT_REVERSAL_ID
, -99)
, -99
,
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))
, AID.INVOICE_ID)
,
DECODE(NVL(AID.PARENT_REVERSAL_ID
, -99)
, -99
,
DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 'AP_INV_DIST'
, 'PO_DISTRIBUTIONS_ALL')
, 'AP_INV_DIST')
,
DECODE(NVL(AID.PARENT_REVERSAL_ID
, -99)
, -99
,
DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, AID.PARENT_REVERSAL_ID
, AID.PO_DISTRIBUTION_ID)
, AID.PARENT_REVERSAL_ID )) ELSE 'N' END "UPG_AP_ENCUM_OPTION"
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, (SELECT ENCUMBRANCE_TYPE_ID
FROM GL_ENCUMBRANCE_TYPES
WHERE ENCUMBRANCE_TYPE_KEY = 'OBLIGATION'
AND ENABLED_FLAG='Y'))
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, POD.CODE_COMBINATION_ID)
/*
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
,
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)) )*/
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
,
(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)) * NVL(POD.RATE
, 1)))
,
ASP.BASE_CURRENCY_CODE) /*BUG7570932*/
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
,
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)
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'ERV'
, 'Y'
, 'N') OVERRIDE_ACCTD_AMT_FLAG
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, AI.EXCHANGE_RATE
, NVL(POD.RATE
, POH.RATE))
, RCV.CURRENCY_CONVERSION_RATE) PO_RCV_INV_CURR_CONV_RATE
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, NVL(AI.EXCHANGE_DATE
, AI.GL_DATE)
, NVL(POD.RATE_DATE
, POH.CREATION_DATE))
,
NVL(RCV.CURRENCY_CONVERSION_DATE
, RCV.TRANSACTION_DATE)) PO_RCV_INV_CURR_CONV_DATE
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, AI.EXCHANGE_RATE_TYPE
, POH.RATE_TYPE)
, RCV.CURRENCY_CONVERSION_TYPE) PO_RCV_INV_CURR_CONV_TYPE
, AID_ERV_REL.DIST_CODE_COMBINATION_ID VARIANCE_PARENT_DIST_CCID--BUG13508145
, DECODE(RCV.LCM_SHIPMENT_LINE_ID
, NULL
, 'NO'
, 'YES') LCM_ENABLED
, RCV.ORGANIZATION_ID
FROM AP_INVOICE_LINES_ALL AIL
,
AP_INVOICES_ALL AI
,
AP_INVOICE_DISTRIBUTIONS_ALL AID
,
AP_INVOICE_DISTRIBUTIONS_ALL AID_ERV_REL
, --BUG13508145
AP_AWT_GROUPS AWTG
,
AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS
,
PO_DISTRIBUTIONS_ALL POD
,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
,
GL_ENCUMBRANCE_TYPES GLET
,
IGC_CC_SLA_BFLOW_PRIOR_V IGC
,
RCV_TRANSACTIONS RCV
,
PO_HEADERS_ALL POH
,
AP_SYSTEM_PARAMETERS_ALL ASP /*BUG7570932*/
WHERE AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
AND AID.INVOICE_ID = AIL.INVOICE_ID
AND AID.INVOICE_ID = AI.INVOICE_ID
AND AID.AWT_GROUP_ID = AWTG.GROUP_ID(+)
AND AID.AWT_RELATED_ID = AWT_RELATED_IDS.INVOICE_DISTRIBUTION_ID(+)
AND NVL(AID.POSTED_FLAG
, 'N') <> 'Y'
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND AID.PO_DISTRIBUTION_ID = IGC.PO_DISTRIBUTION_ID(+)
AND AID.ORG_ID = FSP.ORG_ID
AND GLET.ENCUMBRANCE_TYPE_KEY='INVOICES'
AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+)
AND AID.SET_OF_BOOKS_ID = ASP.SET_OF_BOOKS_ID
AND AID.ORG_ID = ASP.ORG_ID /*BUG7570932*/
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
/* BUG13508145 - START */
AND AID_ERV_REL.INVOICE_ID(+) = AID.INVOICE_ID
AND AID_ERV_REL.INVOICE_DISTRIBUTION_ID(+) = AID.RELATED_ID
/* BUG13508145 - END */
UNION ALL
SELECT ASAT.BC_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(ASAT.PARENT_REVERSAL_ID
,
NULL
, 'N'
, 'Y')
, 'N') */
, '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
, DECODE (IGC.CC_LINE
, NULL
, 201
, IGC.IGC_APPLICATION_ID)
, DECODE (IGC.CC_LINE
, NULL
, 'PO_DISTRIBUTIONS_ALL'
, IGC.CC_DIST_TYPE)
, DECODE (IGC.CC_LINE
, NULL
, DECODE (POD.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE')
, IGC.CC_ENTITY_CODE)
, DECODE (IGC.CC_LINE
, NULL
, ASAT.PO_DISTRIBUTION_ID
, IGC.CC_DIST_ID)
, DECODE (IGC.CC_LINE
, NULL
, DECODE (POD.PO_RELEASE_ID
, NULL
, POD.PO_HEADER_ID
, POD.PO_RELEASE_ID)
, IGC.CC_DOC_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
, DECODE(AIL.LINE_TYPE_LOOKUP_CODE
, 'ERV'
, 'Y'
, 'N') OVERRIDE_ACCTD_AMT_FLAG
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, AI.EXCHANGE_RATE
, NVL(POD.RATE
, POH.RATE))
, RCV.CURRENCY_CONVERSION_RATE) PO_RCV_INV_CURR_CONV_RATE
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, NVL(AI.EXCHANGE_DATE
, AI.GL_DATE)
, NVL(POD.RATE_DATE
, POH.CREATION_DATE))
,
NVL(RCV.CURRENCY_CONVERSION_DATE
, RCV.TRANSACTION_DATE)) PO_RCV_INV_CURR_CONV_DATE
, DECODE(RCV.TRANSACTION_ID
, NULL
, DECODE(POD.PO_DISTRIBUTION_ID
,
NULL
, AI.EXCHANGE_RATE_TYPE
, POH.RATE_TYPE)
, RCV.CURRENCY_CONVERSION_TYPE) P_RCV_INV_CURR_CONV_TYPE
, ASAT_ERV_REL.DIST_CODE_COMBINATION_ID VARIANCE_PARENT_DIST_CCID--BUG13508145
, DECODE(RCV.LCM_SHIPMENT_LINE_ID
, NULL
, 'NO'
, 'YES') LCM_ENABLED
, RCV.ORGANIZATION_ID
FROM AP_INVOICE_LINES_ALL AIL
,
AP_INVOICES_ALL AI
,
AP_SELF_ASSESSED_TAX_DIST_ALL ASAT
,
AP_SELF_ASSESSED_TAX_DIST_ALL ASAT_ERV_REL
, --BUG13508145
AP_AWT_GROUPS AWTG
,
PO_DISTRIBUTIONS_ALL POD
,
RCV_TRANSACTIONS RCV
,
PO_HEADERS_ALL POH
,
IGC_CC_SLA_BFLOW_PRIOR_V IGC
WHERE ASAT.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
AND ASAT.INVOICE_ID = AIL.INVOICE_ID
AND ASAT.INVOICE_ID = AI.INVOICE_ID
AND ASAT.AWT_GROUP_ID = AWTG.GROUP_ID(+)
AND NVL(ASAT.POSTED_FLAG
, 'N') <> 'Y'
AND AIL.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID(+)
AND ASAT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND ASAT.PO_DISTRIBUTION_ID = IGC.PO_DISTRIBUTION_ID(+)
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
/* BUG13508145 - START */
AND ASAT_ERV_REL.INVOICE_ID(+) = ASAT.INVOICE_ID
AND ASAT_ERV_REL.INVOICE_DISTRIBUTION_ID(+) = ASAT.RELATED_ID
/* BUG13508145 - END */
|
|
|
|