VIEW: APPS.AP_MATCH_CORRECTION_DISTS_V

Source

View Text - Preformatted

SELECT AID.ROWID ROW_ID , AID.INVOICE_ID INVOICE_ID , AID.INVOICE_LINE_NUMBER INVOICE_LINE_NUMBER , AID.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID , AID.QUANTITY_INVOICED DIST_QTY_INVOICED , PD.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID , PD.ACCRUED_FLAG ACCRUED_FLAG , PD.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG , PD.AMOUNT_BILLED AMOUNT_BILLED , PD.AMOUNT_ORDERED AMOUNT_ORDERED , PD.AMOUNT_CANCELLED AMOUNT_CANCELLED , PD.AMOUNT_DELIVERED AMOUNT_DELIVERED , AID.ATTRIBUTE1 ATTRIBUTE1 , AID.ATTRIBUTE2 ATTRIBUTE2 , AID.ATTRIBUTE3 ATTRIBUTE3 , AID.ATTRIBUTE4 ATTRIBUTE4 , AID.ATTRIBUTE5 ATTRIBUTE5 , AID.ATTRIBUTE6 ATTRIBUTE6 , AID.ATTRIBUTE7 ATTRIBUTE7 , AID.ATTRIBUTE8 ATTRIBUTE8 , AID.ATTRIBUTE9 ATTRIBUTE9 , AID.ATTRIBUTE10 ATTRIBUTE10 , AID.ATTRIBUTE11 ATTRIBUTE11 , AID.ATTRIBUTE12 ATTRIBUTE12 , AID.ATTRIBUTE13 ATTRIBUTE13 , AID.ATTRIBUTE14 ATTRIBUTE14 , AID.ATTRIBUTE15 ATTRIBUTE15 , AID.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , PD.BOM_RESOURCE_ID BOM_RESOURCE_ID , PD.BUDGET_ACCOUNT_ID BUDGET_ACCOUNT_ID , AID.DIST_CODE_COMBINATION_ID CODE_COMBINATION_ID , AIL.CREATED_BY CREATED_BY , AIL.CREATION_DATE CREATION_DATE , PD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID , PD.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID , PD.DESTINATION_CONTEXT DESTINATION_CONTEXT , PD.DESTINATION_ORGANIZATION_ID DESTINATION_ORGANIZATION_ID , PD.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY , PD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE , PD.DISTRIBUTION_NUM DISTRIBUTION_NUM , PD.ENCUMBERED_AMOUNT ENCUMBERED_AMOUNT , PD.ENCUMBERED_FLAG ENCUMBERED_FLAG , PD.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , PD.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , HOUT.NAME EXPENDITURE_ORGANIZATION , AID.EXPENDITURE_TYPE EXPENDITURE_TYPE , PD.FAILED_FUNDS_LOOKUP_CODE FAILED_FUNDS_LOOKUP_CODE , PD.GL_CANCELLED_DATE GL_CANCELLED_DATE , PD.GL_CLOSED_DATE GL_CLOSED_DATE , PD.GL_ENCUMBERED_DATE GL_ENCUMBERED_DATE , PD.GL_ENCUMBERED_PERIOD_NAME GL_ENCUMBERED_PERIOD_NAME , PD.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT , AIL.LAST_UPDATED_BY LAST_UPDATED_BY , AIL.LAST_UPDATE_DATE LAST_UPDATE_DATE , AIL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , AIL.PO_LINE_LOCATION_ID LINE_LOCATION_ID , AID.ORG_ID ORG_ID , PD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID , PD.PO_HEADER_ID PO_HEADER_ID , PD.PO_LINE_ID PO_LINE_ID , PD.PO_RELEASE_ID PO_RELEASE_ID , PD.PREVENT_ENCUMBRANCE_FLAG PREVENT_ENCUMBRANCE_FLAG , PD.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , PD.PROGRAM_ID PROGRAM_ID , PD.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , PD.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT , AID.PROJECT_ID PROJECT_ID , PAP.SEGMENT1 PROJECT_NAME , PD.QUANTITY_BILLED QUANTITY_BILLED , PD.QUANTITY_CANCELLED QUANTITY_CANCELLED , PD.QUANTITY_DELIVERED QUANTITY_DELIVERED , PD.QUANTITY_ORDERED QUANTITY_ORDERED , PD.RATE RATE , PD.RATE_DATE RATE_DATE , PD.REQUEST_ID REQUEST_ID , PD.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID , PD.REQ_HEADER_REFERENCE_NUM REQ_HEADER_REFERENCE_NUM , PD.REQ_LINE_REFERENCE_NUM REQ_LINE_REFERENCE_NUM , AID.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , PD.SOURCE_DISTRIBUTION_ID SOURCE_DISTRIBUTION_ID , AID.TASK_ID TASK_ID , PAT.TASK_NUMBER TASK_NUMBER , PAT.TASK_NAME TASK_NAME , PD.UNENCUMBERED_AMOUNT UNENCUMBERED_AMOUNT , PD.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY , PD.VARIANCE_ACCOUNT_ID VARIANCE_ACCOUNT_ID , PD.WIP_ENTITY_ID WIP_ENTITY_ID , PD.WIP_LINE_ID WIP_LINE_ID , PD.WIP_OPERATION_SEQ_NUM WIP_OPERATION_SEQ_NUM , PD.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULE_ID , PD.WIP_RESOURCE_SEQ_NUM WIP_RESOURCE_SEQ_NUM , HE.FULL_NAME REQUESTOR FROM AP_INVOICE_LINES_ALL AIL, AP_INVOICE_DISTRIBUTIONS_ALL AID, PO_DISTRIBUTIONS_ALL PD, PER_PEOPLE_F HE, PA_TASKS PAT, PA_PROJECTS_ALL PAP, HR_ORG_UNITS_NO_JOIN HROU, HR_ALL_ORGANIZATION_UNITS_TL HOUT WHERE AIL.INVOICE_ID = AID.INVOICE_ID AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER /* Bug 5585744. Added the following condition */ AND (AID.LINE_TYPE_LOOKUP_CODE IN ('ITEM', 'ACCRUAL') AND AID.PREPAY_DISTRIBUTION_ID IS NULL) AND AIL.PO_LINE_LOCATION_ID = PD.LINE_LOCATION_ID /*4749294 */ AND AID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID AND PD.DELIVER_TO_PERSON_ID = HE.PERSON_ID (+) AND TRUNC(SYSDATE) > HE.EFFECTIVE_START_DATE (+) AND TRUNC(SYSDATE) < HE.EFFECTIVE_END_DATE (+) AND PD.PROJECT_ID = PAP.PROJECT_ID (+) AND PD.TASK_ID = PAT.TASK_ID (+) AND PD.EXPENDITURE_ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND hrou.organization_id = hout.organization_id (+) AND DECODE(hout.organization_id, null, '1', hout.language) = DECODE(hout.organization_id, null, '1', USERENV('LANG'))

View Text - HTML Formatted

SELECT AID.ROWID ROW_ID
, AID.INVOICE_ID INVOICE_ID
, AID.INVOICE_LINE_NUMBER INVOICE_LINE_NUMBER
, AID.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, AID.QUANTITY_INVOICED DIST_QTY_INVOICED
, PD.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID
, PD.ACCRUED_FLAG ACCRUED_FLAG
, PD.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG
, PD.AMOUNT_BILLED AMOUNT_BILLED
, PD.AMOUNT_ORDERED AMOUNT_ORDERED
, PD.AMOUNT_CANCELLED AMOUNT_CANCELLED
, PD.AMOUNT_DELIVERED AMOUNT_DELIVERED
, AID.ATTRIBUTE1 ATTRIBUTE1
, AID.ATTRIBUTE2 ATTRIBUTE2
, AID.ATTRIBUTE3 ATTRIBUTE3
, AID.ATTRIBUTE4 ATTRIBUTE4
, AID.ATTRIBUTE5 ATTRIBUTE5
, AID.ATTRIBUTE6 ATTRIBUTE6
, AID.ATTRIBUTE7 ATTRIBUTE7
, AID.ATTRIBUTE8 ATTRIBUTE8
, AID.ATTRIBUTE9 ATTRIBUTE9
, AID.ATTRIBUTE10 ATTRIBUTE10
, AID.ATTRIBUTE11 ATTRIBUTE11
, AID.ATTRIBUTE12 ATTRIBUTE12
, AID.ATTRIBUTE13 ATTRIBUTE13
, AID.ATTRIBUTE14 ATTRIBUTE14
, AID.ATTRIBUTE15 ATTRIBUTE15
, AID.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PD.BOM_RESOURCE_ID BOM_RESOURCE_ID
, PD.BUDGET_ACCOUNT_ID BUDGET_ACCOUNT_ID
, AID.DIST_CODE_COMBINATION_ID CODE_COMBINATION_ID
, AIL.CREATED_BY CREATED_BY
, AIL.CREATION_DATE CREATION_DATE
, PD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID
, PD.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID
, PD.DESTINATION_CONTEXT DESTINATION_CONTEXT
, PD.DESTINATION_ORGANIZATION_ID DESTINATION_ORGANIZATION_ID
, PD.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY
, PD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE
, PD.DISTRIBUTION_NUM DISTRIBUTION_NUM
, PD.ENCUMBERED_AMOUNT ENCUMBERED_AMOUNT
, PD.ENCUMBERED_FLAG ENCUMBERED_FLAG
, PD.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, PD.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, HOUT.NAME EXPENDITURE_ORGANIZATION
, AID.EXPENDITURE_TYPE EXPENDITURE_TYPE
, PD.FAILED_FUNDS_LOOKUP_CODE FAILED_FUNDS_LOOKUP_CODE
, PD.GL_CANCELLED_DATE GL_CANCELLED_DATE
, PD.GL_CLOSED_DATE GL_CLOSED_DATE
, PD.GL_ENCUMBERED_DATE GL_ENCUMBERED_DATE
, PD.GL_ENCUMBERED_PERIOD_NAME GL_ENCUMBERED_PERIOD_NAME
, PD.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, AIL.LAST_UPDATED_BY LAST_UPDATED_BY
, AIL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AIL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AIL.PO_LINE_LOCATION_ID LINE_LOCATION_ID
, AID.ORG_ID ORG_ID
, PD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, PD.PO_HEADER_ID PO_HEADER_ID
, PD.PO_LINE_ID PO_LINE_ID
, PD.PO_RELEASE_ID PO_RELEASE_ID
, PD.PREVENT_ENCUMBRANCE_FLAG PREVENT_ENCUMBRANCE_FLAG
, PD.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PD.PROGRAM_ID PROGRAM_ID
, PD.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PD.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT
, AID.PROJECT_ID PROJECT_ID
, PAP.SEGMENT1 PROJECT_NAME
, PD.QUANTITY_BILLED QUANTITY_BILLED
, PD.QUANTITY_CANCELLED QUANTITY_CANCELLED
, PD.QUANTITY_DELIVERED QUANTITY_DELIVERED
, PD.QUANTITY_ORDERED QUANTITY_ORDERED
, PD.RATE RATE
, PD.RATE_DATE RATE_DATE
, PD.REQUEST_ID REQUEST_ID
, PD.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID
, PD.REQ_HEADER_REFERENCE_NUM REQ_HEADER_REFERENCE_NUM
, PD.REQ_LINE_REFERENCE_NUM REQ_LINE_REFERENCE_NUM
, AID.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PD.SOURCE_DISTRIBUTION_ID SOURCE_DISTRIBUTION_ID
, AID.TASK_ID TASK_ID
, PAT.TASK_NUMBER TASK_NUMBER
, PAT.TASK_NAME TASK_NAME
, PD.UNENCUMBERED_AMOUNT UNENCUMBERED_AMOUNT
, PD.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY
, PD.VARIANCE_ACCOUNT_ID VARIANCE_ACCOUNT_ID
, PD.WIP_ENTITY_ID WIP_ENTITY_ID
, PD.WIP_LINE_ID WIP_LINE_ID
, PD.WIP_OPERATION_SEQ_NUM WIP_OPERATION_SEQ_NUM
, PD.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULE_ID
, PD.WIP_RESOURCE_SEQ_NUM WIP_RESOURCE_SEQ_NUM
, HE.FULL_NAME REQUESTOR
FROM AP_INVOICE_LINES_ALL AIL
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, PO_DISTRIBUTIONS_ALL PD
, PER_PEOPLE_F HE
, PA_TASKS PAT
, PA_PROJECTS_ALL PAP
, HR_ORG_UNITS_NO_JOIN HROU
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
WHERE AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER /* BUG 5585744. ADDED THE FOLLOWING CONDITION */
AND (AID.LINE_TYPE_LOOKUP_CODE IN ('ITEM'
, 'ACCRUAL')
AND AID.PREPAY_DISTRIBUTION_ID IS NULL)
AND AIL.PO_LINE_LOCATION_ID = PD.LINE_LOCATION_ID /*4749294 */
AND AID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID
AND PD.DELIVER_TO_PERSON_ID = HE.PERSON_ID (+)
AND TRUNC(SYSDATE) > HE.EFFECTIVE_START_DATE (+)
AND TRUNC(SYSDATE) < HE.EFFECTIVE_END_DATE (+)
AND PD.PROJECT_ID = PAP.PROJECT_ID (+)
AND PD.TASK_ID = PAT.TASK_ID (+)
AND PD.EXPENDITURE_ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND HROU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, HOUT.LANGUAGE) = DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))