AP_UNMATCHED_CORRECTION

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
INVOICE_ID OBJECT_VERSION_NUMBER INVOICE_NUM VENDOR_ID VENDOR_SITE_ID ORG_ID LINE_NUMBER INVOICE_DATE INVOICE_AMOUNT INVOICE_CURRENCY_CODE LINE_TYPE_LOOKUP_CODE DESCRIPTION QUANTITY_INVOICED UNIT_PRICE AMOUNT ITEM_DESCRIPTION UNIT_MEAS_LOOKUP_CODE REQUESTER_ID INVENTORY_ITEM_ID SERIAL_NUMBER MANUFACTURER MODEL_NUMBER ACCOUNT_SEGMENT BALANCING_SEGMENT COST_CENTER_SEGMENT OVERLAY_DIST_CODE_CONCAT DEFAULT_DIST_CCID ASSETS_TRACKING_FLAG ASSET_BOOK_TYPE_CODE ASSET_CATEGORY_ID REFERENCE_1 REFERENCE_2 MERCHANT_DOCUMENT_NUMBER MERCHANT_NAME MERCHANT_REFERENCE MERCHANT_TAX_REG_NUMBER MERCHANT_TAXPAYER_ID COUNTRY_OF_SUPPLY CREDIT_CARD_TRX_ID COMPANY_PREPAID_INVOICE_ID CC_REVERSAL_FLAG ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 SHIP_TO_LOCATION_ID PRIMARY_INTENDED_USE PRODUCT_FISC_CLASSIFICATION PRODUCT_TYPE PRODUCT_CATEGORY USER_DEFINED_FISC_CLASS PURCHASING_CATEGORY_ID PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 TAX_CLASSIFICATION_CODE TAX_ALREADY_CALCULATED_FLAG ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 AWT_GROUP_ID TRX_BUSINESS_CATEGORY BUDGET_DATE LCM_ENABLED_FLAG SHIP_FROM_LOCATION_ID FINAL_DISCHARGE_LOCATION_ID DEF_ACCTG_START_DATE DEF_ACCTG_END_DATE DEF_ACCTG_ACCRUAL_CCID PROD_FC_CATEG_ID

Name

INVOICE_ID

OBJECT_VERSION_NUMBER

INVOICE_NUM

VENDOR_ID

VENDOR_SITE_ID

ORG_ID

LINE_NUMBER

INVOICE_DATE

INVOICE_AMOUNT

INVOICE_CURRENCY_CODE

LINE_TYPE_LOOKUP_CODE

DESCRIPTION

QUANTITY_INVOICED

UNIT_PRICE

AMOUNT

ITEM_DESCRIPTION

UNIT_MEAS_LOOKUP_CODE

REQUESTER_ID

INVENTORY_ITEM_ID

SERIAL_NUMBER

MANUFACTURER

MODEL_NUMBER

ACCOUNT_SEGMENT

BALANCING_SEGMENT

COST_CENTER_SEGMENT

OVERLAY_DIST_CODE_CONCAT

DEFAULT_DIST_CCID

ASSETS_TRACKING_FLAG

ASSET_BOOK_TYPE_CODE

ASSET_CATEGORY_ID

REFERENCE_1

REFERENCE_2

MERCHANT_DOCUMENT_NUMBER

MERCHANT_NAME

MERCHANT_REFERENCE

MERCHANT_TAX_REG_NUMBER

MERCHANT_TAXPAYER_ID

COUNTRY_OF_SUPPLY

CREDIT_CARD_TRX_ID

COMPANY_PREPAID_INVOICE_ID

CC_REVERSAL_FLAG

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

SHIP_TO_LOCATION_ID

PRIMARY_INTENDED_USE

PRODUCT_FISC_CLASSIFICATION

PRODUCT_TYPE

PRODUCT_CATEGORY

USER_DEFINED_FISC_CLASS

PURCHASING_CATEGORY_ID

PJC_CONTEXT_CATEGORY

PJC_PROJECT_ID

PJC_TASK_ID

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_WORK_TYPE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_RESERVED_ATTRIBUTE1

PJC_RESERVED_ATTRIBUTE2

PJC_RESERVED_ATTRIBUTE3

PJC_RESERVED_ATTRIBUTE4

PJC_RESERVED_ATTRIBUTE6

PJC_RESERVED_ATTRIBUTE5

PJC_RESERVED_ATTRIBUTE7

PJC_RESERVED_ATTRIBUTE8

PJC_RESERVED_ATTRIBUTE9

PJC_RESERVED_ATTRIBUTE10

PJC_USER_DEF_ATTRIBUTE1

PJC_USER_DEF_ATTRIBUTE2

PJC_USER_DEF_ATTRIBUTE3

PJC_USER_DEF_ATTRIBUTE4

PJC_USER_DEF_ATTRIBUTE5

PJC_USER_DEF_ATTRIBUTE6

PJC_USER_DEF_ATTRIBUTE7

PJC_USER_DEF_ATTRIBUTE8

PJC_USER_DEF_ATTRIBUTE9

PJC_USER_DEF_ATTRIBUTE10

TAX_CLASSIFICATION_CODE

TAX_ALREADY_CALCULATED_FLAG

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

AWT_GROUP_ID

TRX_BUSINESS_CATEGORY

BUDGET_DATE

LCM_ENABLED_FLAG

SHIP_FROM_LOCATION_ID

FINAL_DISCHARGE_LOCATION_ID

DEF_ACCTG_START_DATE

DEF_ACCTG_END_DATE

DEF_ACCTG_ACCRUAL_CCID

PROD_FC_CATEG_ID

Query

SQL_Statement
SELECT AI.INVOICE_ID, AI.OBJECT_VERSION_NUMBER, AI.INVOICE_NUM, AI.VENDOR_ID, AI.VENDOR_SITE_ID, AI.ORG_ID, AIL.LINE_NUMBER, AI.INVOICE_DATE, AI.INVOICE_AMOUNT, AI.INVOICE_CURRENCY_CODE, AIL.LINE_TYPE_LOOKUP_CODE, AIL.DESCRIPTION, AIL.QUANTITY_INVOICED, AIL.UNIT_PRICE, AIL.AMOUNT, AIL.ITEM_DESCRIPTION, AIL.UNIT_MEAS_LOOKUP_CODE, AIL.REQUESTER_ID, AIL.INVENTORY_ITEM_ID, AIL.SERIAL_NUMBER, AIL.MANUFACTURER, AIL.MODEL_NUMBER, AIL.ACCOUNT_SEGMENT, AIL.BALANCING_SEGMENT, AIL.COST_CENTER_SEGMENT, AIL.OVERLAY_DIST_CODE_CONCAT, AIL.DEFAULT_DIST_CCID, AIL.ASSETS_TRACKING_FLAG, AIL.ASSET_BOOK_TYPE_CODE, AIL.ASSET_CATEGORY_ID, AIL.REFERENCE_1, AIL.REFERENCE_2, AIL.MERCHANT_DOCUMENT_NUMBER, AIL.MERCHANT_NAME, AIL.MERCHANT_REFERENCE, AIL.MERCHANT_TAX_REG_NUMBER, AIL.MERCHANT_TAXPAYER_ID, AIL.COUNTRY_OF_SUPPLY, AIL.CREDIT_CARD_TRX_ID, AIL.COMPANY_PREPAID_INVOICE_ID, AIL.CC_REVERSAL_FLAG, 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.SHIP_TO_LOCATION_ID, AIL.PRIMARY_INTENDED_USE, AIL.PRODUCT_FISC_CLASSIFICATION, AIL.PRODUCT_TYPE, AIL.PRODUCT_CATEGORY, AIL.USER_DEFINED_FISC_CLASS, AIL.PURCHASING_CATEGORY_ID, AIL.PJC_CONTEXT_CATEGORY, AIL.PJC_PROJECT_ID, AIL.PJC_TASK_ID, AIL.PJC_EXPENDITURE_TYPE_ID, AIL.PJC_EXPENDITURE_ITEM_DATE, AIL.PJC_ORGANIZATION_ID, AIL.PJC_BILLABLE_FLAG, AIL.PJC_CAPITALIZABLE_FLAG, AIL.PJC_WORK_TYPE_ID, AIL.PJC_CONTRACT_ID, AIL.PJC_CONTRACT_LINE_ID, AIL.PJC_FUNDING_ALLOCATION_ID, AIL.PJC_RESERVED_ATTRIBUTE1, AIL.PJC_RESERVED_ATTRIBUTE2, AIL.PJC_RESERVED_ATTRIBUTE3, AIL.PJC_RESERVED_ATTRIBUTE4, AIL.PJC_RESERVED_ATTRIBUTE6, AIL.PJC_RESERVED_ATTRIBUTE5, AIL.PJC_RESERVED_ATTRIBUTE7, AIL.PJC_RESERVED_ATTRIBUTE8, AIL.PJC_RESERVED_ATTRIBUTE9, AIL.PJC_RESERVED_ATTRIBUTE10, AIL.PJC_USER_DEF_ATTRIBUTE1, AIL.PJC_USER_DEF_ATTRIBUTE2, AIL.PJC_USER_DEF_ATTRIBUTE3, AIL.PJC_USER_DEF_ATTRIBUTE4, AIL.PJC_USER_DEF_ATTRIBUTE5, AIL.PJC_USER_DEF_ATTRIBUTE6, AIL.PJC_USER_DEF_ATTRIBUTE7, AIL.PJC_USER_DEF_ATTRIBUTE8, AIL.PJC_USER_DEF_ATTRIBUTE9, AIL.PJC_USER_DEF_ATTRIBUTE10, AIL.TAX_CLASSIFICATION_CODE, AIL.TAX_ALREADY_CALCULATED_FLAG, AIL.ATTRIBUTE_NUMBER1, AIL.ATTRIBUTE_NUMBER2, AIL.ATTRIBUTE_NUMBER3, AIL.ATTRIBUTE_NUMBER4, AIL.ATTRIBUTE_NUMBER5, AIL.ATTRIBUTE_DATE1, AIL.ATTRIBUTE_DATE2, AIL.ATTRIBUTE_DATE3, AIL.ATTRIBUTE_DATE4, AIL.ATTRIBUTE_DATE5, AIL.AWT_GROUP_ID, AIL.TRX_BUSINESS_CATEGORY, AIL.BUDGET_DATE, AIL.LCM_ENABLED_FLAG, AIL.SHIP_FROM_LOCATION_ID, AIL.FINAL_DISCHARGE_LOCATION_ID, AIL.DEF_ACCTG_START_DATE, AIL.DEF_ACCTG_END_DATE, AIL.DEF_ACCTG_ACCRUAL_CCID, AIL.PROD_FC_CATEG_ID FROM AP_INVOICES_ALL AI, AP_INVOICE_LINES_ALL AIL WHERE AI.INVOICE_ID = AIL.INVOICE_ID AND AI.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD' AND AI.CANCELLED_DATE IS NULL AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND NVL(AIL.MATCH_TYPE, 'NOT_MATCHED') = 'NOT_MATCHED' AND NVL(AIL.CANCELLED_FLAG, 'N') != 'Y' AND NVL(AIL.DISCARDED_FLAG, 'N') != 'Y' AND (AIL.GENERATE_DISTS = 'D' OR (AIL.GENERATE_DISTS = 'N' AND AIL.AMOUNT = (SELECT SUM(NVL(AID.AMOUNT, 0)) FROM AP_INVOICE_DISTRIBUTIONS_ALL AID WHERE AID.INVOICE_ID = INVOICE_ID AND AID.INVOICE_LINE_NUMBER = LINE_NUMBER))) AND AP_MATCHING_UTILS_PKG.GET_AVAIL_LINE_CORR_AMOUNT(AIL.INVOICE_ID, AIL.LINE_NUMBER) > 0

SQL_Statement

SELECT

AI.INVOICE_ID,

AI.OBJECT_VERSION_NUMBER,

AI.INVOICE_NUM,

AI.VENDOR_ID,

AI.VENDOR_SITE_ID,

AI.ORG_ID,

AIL.LINE_NUMBER,

AI.INVOICE_DATE,

AI.INVOICE_AMOUNT,

AI.INVOICE_CURRENCY_CODE,

AIL.LINE_TYPE_LOOKUP_CODE,

AIL.DESCRIPTION,

AIL.QUANTITY_INVOICED,

AIL.UNIT_PRICE,

AIL.AMOUNT,

AIL.ITEM_DESCRIPTION,

AIL.UNIT_MEAS_LOOKUP_CODE,

AIL.REQUESTER_ID,

AIL.INVENTORY_ITEM_ID,

AIL.SERIAL_NUMBER,

AIL.MANUFACTURER,

AIL.MODEL_NUMBER,

AIL.ACCOUNT_SEGMENT,

AIL.BALANCING_SEGMENT,

AIL.COST_CENTER_SEGMENT,

AIL.OVERLAY_DIST_CODE_CONCAT,

AIL.DEFAULT_DIST_CCID,

AIL.ASSETS_TRACKING_FLAG,

AIL.ASSET_BOOK_TYPE_CODE,

AIL.ASSET_CATEGORY_ID,

AIL.REFERENCE_1,

AIL.REFERENCE_2,

AIL.MERCHANT_DOCUMENT_NUMBER,

AIL.MERCHANT_NAME,

AIL.MERCHANT_REFERENCE,

AIL.MERCHANT_TAX_REG_NUMBER,

AIL.MERCHANT_TAXPAYER_ID,

AIL.COUNTRY_OF_SUPPLY,

AIL.CREDIT_CARD_TRX_ID,

AIL.COMPANY_PREPAID_INVOICE_ID,

AIL.CC_REVERSAL_FLAG,

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.SHIP_TO_LOCATION_ID,

AIL.PRIMARY_INTENDED_USE,

AIL.PRODUCT_FISC_CLASSIFICATION,

AIL.PRODUCT_TYPE,

AIL.PRODUCT_CATEGORY,

AIL.USER_DEFINED_FISC_CLASS,

AIL.PURCHASING_CATEGORY_ID,

AIL.PJC_CONTEXT_CATEGORY,

AIL.PJC_PROJECT_ID,

AIL.PJC_TASK_ID,

AIL.PJC_EXPENDITURE_TYPE_ID,

AIL.PJC_EXPENDITURE_ITEM_DATE,

AIL.PJC_ORGANIZATION_ID,

AIL.PJC_BILLABLE_FLAG,

AIL.PJC_CAPITALIZABLE_FLAG,

AIL.PJC_WORK_TYPE_ID,

AIL.PJC_CONTRACT_ID,

AIL.PJC_CONTRACT_LINE_ID,

AIL.PJC_FUNDING_ALLOCATION_ID,

AIL.PJC_RESERVED_ATTRIBUTE1,

AIL.PJC_RESERVED_ATTRIBUTE2,

AIL.PJC_RESERVED_ATTRIBUTE3,

AIL.PJC_RESERVED_ATTRIBUTE4,

AIL.PJC_RESERVED_ATTRIBUTE6,

AIL.PJC_RESERVED_ATTRIBUTE5,

AIL.PJC_RESERVED_ATTRIBUTE7,

AIL.PJC_RESERVED_ATTRIBUTE8,

AIL.PJC_RESERVED_ATTRIBUTE9,

AIL.PJC_RESERVED_ATTRIBUTE10,

AIL.PJC_USER_DEF_ATTRIBUTE1,

AIL.PJC_USER_DEF_ATTRIBUTE2,

AIL.PJC_USER_DEF_ATTRIBUTE3,

AIL.PJC_USER_DEF_ATTRIBUTE4,

AIL.PJC_USER_DEF_ATTRIBUTE5,

AIL.PJC_USER_DEF_ATTRIBUTE6,

AIL.PJC_USER_DEF_ATTRIBUTE7,

AIL.PJC_USER_DEF_ATTRIBUTE8,

AIL.PJC_USER_DEF_ATTRIBUTE9,

AIL.PJC_USER_DEF_ATTRIBUTE10,

AIL.TAX_CLASSIFICATION_CODE,

AIL.TAX_ALREADY_CALCULATED_FLAG,

AIL.ATTRIBUTE_NUMBER1,

AIL.ATTRIBUTE_NUMBER2,

AIL.ATTRIBUTE_NUMBER3,

AIL.ATTRIBUTE_NUMBER4,

AIL.ATTRIBUTE_NUMBER5,

AIL.ATTRIBUTE_DATE1,

AIL.ATTRIBUTE_DATE2,

AIL.ATTRIBUTE_DATE3,

AIL.ATTRIBUTE_DATE4,

AIL.ATTRIBUTE_DATE5,

AIL.AWT_GROUP_ID,

AIL.TRX_BUSINESS_CATEGORY,

AIL.BUDGET_DATE,

AIL.LCM_ENABLED_FLAG,

AIL.SHIP_FROM_LOCATION_ID,

AIL.FINAL_DISCHARGE_LOCATION_ID,

AIL.DEF_ACCTG_START_DATE,

AIL.DEF_ACCTG_END_DATE,

AIL.DEF_ACCTG_ACCRUAL_CCID,

AIL.PROD_FC_CATEG_ID

FROM

AP_INVOICES_ALL AI,

AP_INVOICE_LINES_ALL AIL

WHERE

AI.INVOICE_ID = AIL.INVOICE_ID AND AI.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD' AND AI.CANCELLED_DATE IS NULL AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND NVL(AIL.MATCH_TYPE, 'NOT_MATCHED') = 'NOT_MATCHED' AND NVL(AIL.CANCELLED_FLAG, 'N') != 'Y' AND NVL(AIL.DISCARDED_FLAG, 'N') != 'Y' AND (AIL.GENERATE_DISTS = 'D' OR (AIL.GENERATE_DISTS = 'N' AND AIL.AMOUNT = (SELECT

SUM(NVL(AID.AMOUNT, 0))

FROM

AP_INVOICE_DISTRIBUTIONS_ALL AID

WHERE

AID.INVOICE_ID = INVOICE_ID AND AID.INVOICE_LINE_NUMBER = LINE_NUMBER))) AND AP_MATCHING_UTILS_PKG.GET_AVAIL_LINE_CORR_AMOUNT(AIL.INVOICE_ID, AIL.LINE_NUMBER) > 0