oracle-EBS-PL/sql AP 发票中匹配的po明细按发票

财务人员做了发票后，有时候可能忘记该po是否做过匹配或者匹配的具体信息，可以通过以下语句查询

 SELECT
  AIL.ROWID                ROW_ID
  ,AIL.INVOICE_ID          INVOICE_ID
  ,AIL.LINE_NUMBER         LINE_NUMBER
  ,AIL.LINE_TYPE_LOOKUP_CODE LINE_TYPE_LOOKUP_CODE
  ,ALC1.DISPLAYED_FIELD    LINE_TYPE
  ,AIL.REQUESTER_ID        REQUESTER_ID
  ,AIL.DESCRIPTION         DESCRIPTION
  ,AIL.LINE_SOURCE         LINE_SOURCE
  ,AIL.ORG_ID              ORG_ID
  ,AIL.LINE_GROUP_NUMBER   LINE_GROUP_NUMBER
  ,AIL.INVENTORY_ITEM_ID   INVENTORY_ITEM_ID
  ,AIL.ITEM_DESCRIPTION    ITEM_DESCRIPTION
  ,AIL.SERIAL_NUMBER       SERIAL_NUMBER
  ,AIL.MANUFACTURER        MANUFACTURER
  ,AIL.MODEL_NUMBER        MODEL_NUMBER
  ,AIL.WARRANTY_NUMBER     WARRANTY_NUMBER
  ,AIL.GENERATE_DISTS      GENERATE_DISTRIBUTIONS
  ,AIL.MATCH_TYPE          MATCH_TYPE
  ,AIL.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
  ,APID.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
  ,AIL.ACCOUNT_SEGMENT     ACCOUNT_SEGMENT
  ,AIL.BALANCING_SEGMENT   BALANCING_SEGMENT
  ,AIL.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
  ,AIL.OVERLAY_DIST_CODE_CONCAT OVERLAY_DIST_CODE_CONCAT
  ,AIL.DEFAULT_DIST_CCID   DEFAULT_DIST_CCID
  ,AIL.PRORATE_ACROSS_ALL_ITEMS PRORATE_ACROSS_ALL_ITEMS
  ,AIL.ACCOUNTING_DATE     GL_DATE
  ,AIL.PERIOD_NAME         PERIOD_NAME
  ,AIL.DEFERRED_ACCTG_FLAG DEFERRED_ACCTG_FLAG
  ,AIL.DEF_ACCTG_START_DATE DEFERRED_START_DATE
  ,AIL.DEF_ACCTG_END_DATE   DEFERRED_END_DATE
  ,AIL.DEF_ACCTG_NUMBER_OF_PERIODS DEFERRED_NUMBER_OF_PERIODS
  ,AIL.DEF_ACCTG_PERIOD_TYPE DEFERRED_PERIOD_TYPE
  ,AIL.SET_OF_BOOKS_ID     SET_OF_BOOKS_ID
  ,AIL.AMOUNT              AMOUNT
  ,AIL.BASE_AMOUNT         BASE_AMOUNT
  ,AIL.ROUNDING_AMT        ROUNDING_AMOUNT
  ,AIL.QUANTITY_INVOICED   QUANTITY_INVOICED
  ,AIL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE
  ,AIL.UNIT_PRICE          UNIT_PRICE
  ,AIL.WFAPPROVAL_STATUS   WFAPPROVAL_STATUS_LOOKUP_CODE
  ,ALC2.DISPLAYED_FIELD    WFAPPROVAL_STATUS_DISP
  ,AIL.DISCARDED_FLAG      DISCARDED_FLAG
  ,AIL.ORIGINAL_AMOUNT     ORIGINAL_AMOUNT
  ,AIL.ORIGINAL_BASE_AMOUNT ORIGINAL_BASE_AMOUNT
  ,AIL.ORIGINAL_ROUNDING_AMT ORIGINAL_ROUNDING_AMOUNT
  ,AIL.CANCELLED_FLAG      CANCELLED_FLAG
  ,AIL.INCOME_TAX_REGION   INCOME_TAX_REGION
  ,AIL.TYPE_1099           INCOME_TAX_TYPE
  ,AIL.STAT_AMOUNT         STAT_AMOUNT
  ,AIL.PREPAY_INVOICE_ID   PREPAY_INVOICE_ID
  ,AIP.INVOICE_NUM         PREPAY_INVOICE_NUM
  ,AIL.PREPAY_LINE_NUMBER  PREPAY_LINE_NUMBER
  ,AIL.INVOICE_INCLUDES_PREPAY_FLAG INVOICE_INCLUDES_PREPAY_FLAG
  ,AIL.CORRECTED_INV_ID    CORRECTED_INV_ID
  ,AIC.INVOICE_NUM         CORRECTED_INVOICE_NUM
  ,AIL.CORRECTED_LINE_NUMBER CORRECTED_LINE_NUMBER
  ,AIL.PO_HEADER_ID        PO_HEADER_ID
  ,NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER /*9481666 - CLM*/
  ,AIL.PO_LINE_ID          PO_LINE_ID
  ,NVL(PL.LINE_NUM_DISPLAY, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER /*9481666 - CLM*/
  ,AIL.PO_RELEASE_ID       PO_RELEASE_ID
  ,PR.RELEASE_NUM          PO_RELEASE_NUMBER
  ,AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID
  ,PLL.SHIPMENT_NUM        PO_SHIPMENT_NUMBER
  ,ALC3.DISPLAYED_FIELD    MATCHING_BASIS               /* Amount Based Matching */
  ,PLL.MATCHING_BASIS      MATCHING_BASIS_LOOKUP_CODE   /* Amount Based Matching */
  ,AIL.PO_DISTRIBUTION_ID  PO_DISTRIBUTION_ID
  ,PD.DISTRIBUTION_NUM     PO_DISTRIBUTION_NUMBER
  ,AIL.RCV_TRANSACTION_ID  RCV_TRANSACTION_ID
  ,RSH.RECEIPT_NUM         RECEIPT_NUMBER
  ,RSL.LINE_NUM            RECEIPT_LINE_NUMBER
  ,AIL.FINAL_MATCH_FLAG    FINAL_MATCH_FLAG
  ,AIL.ASSETS_TRACKING_FLAG ASSETS_TRACKING_FLAG
  ,AIL.ASSET_BOOK_TYPE_CODE ASSET_BOOK_TYPE_CODE
  ,FABC.BOOK_TYPE_NAME     ASSET_BOOK_NAME
  ,AIL.ASSET_CATEGORY_ID   ASSET_CATEGORY_ID
  ,AIL.PROJECT_ID          PROJECT_ID
  ,PAP.SEGMENT1            PROJECT
  ,AIL.TASK_ID             TASK_ID
  ,PAT.TASK_NUMBER         TASK
  ,AIL.EXPENDITURE_TYPE    EXPENDITURE_TYPE
  ,AIL.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
  ,AIL.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
  ,HOU.NAME                EXPENDITURE_ORGANIZATION_NAME
  ,AIL.PA_QUANTITY         PA_QUANTITY
  ,AIL.PA_CC_AR_INVOICE_ID PA_CC_AR_INVOICE_ID
  ,AIII.INVOICE_NUM        INTERCOMPANY_INVOICE_NUM
  ,AIL.PA_CC_AR_INVOICE_LINE_NUM INTERCOMPANY_INVOICE_LINE_NUM
  ,AIL.PA_CC_PROCESSED_CODE PROCESSED_CODE
  ,AIL.AWARD_ID            AWARD_ID
  ,AIL.AWT_GROUP_ID        AWT_GROUP_ID
  ,AWT.NAME                AWT_GROUP_NAME
  ,AIL.REFERENCE_1         REFERENCE_1
  ,AIL.REFERENCE_2         REFERENCE_2
  ,AIL.RECEIPT_VERIFIED_FLAG RECEIPT_VERIFIED_FLAG
  ,AIL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
  ,AIL.RECEIPT_MISSING_FLAG  RECEIPT_MISSING_FLAG
  ,AIL.JUSTIFICATION       JUSTIFICATION
  ,AIL.EXPENSE_GROUP       EXPENSE_GROUP
  ,AIL.START_EXPENSE_DATE  START_EXPENSE_DATE
  ,AIL.END_EXPENSE_DATE    END_EXPENSE_DATE
  ,AIL.RECEIPT_CURRENCY_CODE RECEIPT_CURRENCY_CODE
  ,AIL.RECEIPT_CONVERSION_RATE RECEIPT_CONVERSION_RATE
  ,AIL.RECEIPT_CURRENCY_AMOUNT RECEIPT_CURRENCY_AMOUNT
  ,AIL.DAILY_AMOUNT        DAILY_AMOUNT
  ,AIL.WEB_PARAMETER_ID    WEB_PARAMETER_ID
  ,AIL.ADJUSTMENT_REASON   ADJUSTMENT_REASON
  ,AIL.MERCHANT_DOCUMENT_NUMBER MERCHANT_DOCUMENT_NUMBER
  ,AIL.MERCHANT_NAME       MERCHANT_NAME
  ,AIL.MERCHANT_REFERENCE  MERCHANT_REFERENCE
  ,AIL.MERCHANT_TAX_REG_NUMBER MERCHANT_TAX_REG_NUMBER
  ,AIL.MERCHANT_TAXPAYER_ID MERCHANT_TAXPAYER_ID
  ,AIL.COUNTRY_OF_SUPPLY   COUNTRY_OF_SUPPLY
  ,AIL.CREDIT_CARD_TRX_ID  CREDIT_CARD_TRX_ID
  ,AIL.COMPANY_PREPAID_INVOICE_ID COMPANY_PREPAID_INVOICE_ID
  ,AIL.CC_REVERSAL_FLAG    CC_REVERSAL_FLAG
  ,AIL.CREATION_DATE       CREATION_DATE
  ,AIL.CREATED_BY          CREATED_BY
  ,AIL.LAST_UPDATED_BY     LAST_UPDATED_BY
  ,AIL.LAST_UPDATE_DATE    LAST_UPDATE_DATE
  ,AIL.LAST_UPDATE_LOGIN   LAST_UPDATE_LOGIN
  ,AIL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
  ,AIL.PROGRAM_ID          PROGRAM_ID
  ,AIL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
  ,AIL.REQUEST_ID          REQUEST_ID
  ,AP_INVOICE_LINES_UTILITY_PKG.Get_Packet_Id(AIL.Invoice_Id, AIL.Line_Number)
                           QUERY_PACKET_ID
  ,NVL(AP_INVOICE_LINES_UTILITY_PKG.Get_Approval_Status(AIL.Invoice_Id, AIL.Line_Number)
            , 'NEVER APPROVED') VALIDATION_STATUS
  ,NVL(AP_INVOICE_LINES_UTILITY_PKG.Get_Encumbered_Flag(AIL.Invoice_Id, AIL.Line_Number)
              ,'')         ENCUMBRANCE_FLAG
  ,NVL(AP_INVOICE_LINES_UTILITY_PKG.Get_Posting_Status(AIL.Invoice_Id, AIL.Line_Number)
              ,'N')        ACCOUNTING_FLAG
  ,AIL.ATTRIBUTE_CATEGORY  ATTRIBUTE_CATEGORY
  ,AIL.ATTRIBUTE1          ATTRIBUTE1
  ,AIL.ATTRIBUTE2          ATTRIBUTE2
  ,AIL.ATTRIBUTE3          ATTRIBUTE3
  ,AIL.ATTRIBUTE4          ATTRIBUTE4
  ,AIL.ATTRIBUTE5          ATTRIBUTE5
  ,AIL.ATTRIBUTE6          ATTRIBUTE6
  ,AIL.ATTRIBUTE7          ATTRIBUTE7
  ,AIL.ATTRIBUTE8          ATTRIBUTE8
  ,AIL.ATTRIBUTE9          ATTRIBUTE9
  ,AIL.ATTRIBUTE10         ATTRIBUTE10
  ,AIL.ATTRIBUTE11         ATTRIBUTE11
  ,AIL.ATTRIBUTE12         ATTRIBUTE12
  ,AIL.ATTRIBUTE13         ATTRIBUTE13
  ,AIL.ATTRIBUTE14         ATTRIBUTE14
  ,AIL.ATTRIBUTE15         ATTRIBUTE15
  ,AIL.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
  ,AIL.GLOBAL_ATTRIBUTE1   GLOBAL_ATTRIBUTE1
  ,AIL.GLOBAL_ATTRIBUTE2   GLOBAL_ATTRIBUTE2
  ,AIL.GLOBAL_ATTRIBUTE3   GLOBAL_ATTRIBUTE3
  ,AIL.GLOBAL_ATTRIBUTE4   GLOBAL_ATTRIBUTE4
  ,AIL.GLOBAL_ATTRIBUTE5   GLOBAL_ATTRIBUTE5
  ,AIL.GLOBAL_ATTRIBUTE6   GLOBAL_ATTRIBUTE6
  ,AIL.GLOBAL_ATTRIBUTE7   GLOBAL_ATTRIBUTE7
  ,AIL.GLOBAL_ATTRIBUTE8   GLOBAL_ATTRIBUTE8
  ,AIL.GLOBAL_ATTRIBUTE9   GLOBAL_ATTRIBUTE9
  ,AIL.GLOBAL_ATTRIBUTE10  GLOBAL_ATTRIBUTE10
  ,AIL.GLOBAL_ATTRIBUTE11  GLOBAL_ATTRIBUTE11
  ,AIL.GLOBAL_ATTRIBUTE12  GLOBAL_ATTRIBUTE12
  ,AIL.GLOBAL_ATTRIBUTE13  GLOBAL_ATTRIBUTE13
  ,AIL.GLOBAL_ATTRIBUTE14  GLOBAL_ATTRIBUTE14
  ,AIL.GLOBAL_ATTRIBUTE15  GLOBAL_ATTRIBUTE15
  ,AIL.GLOBAL_ATTRIBUTE16  GLOBAL_ATTRIBUTE16
  ,AIL.GLOBAL_ATTRIBUTE17  GLOBAL_ATTRIBUTE17
  ,AIL.GLOBAL_ATTRIBUTE18  GLOBAL_ATTRIBUTE18
  ,AIL.GLOBAL_ATTRIBUTE19  GLOBAL_ATTRIBUTE19
  ,AIL.GLOBAL_ATTRIBUTE20  GLOBAl_ATTRIBUTE20
  ,AP_INVOICES_UTILITY_PKG.GET_MAX_DIST_LINE_NUM(AI.INVOICE_ID,
						 AIL.LINE_NUMBER)
  ,AIL.PRIMARY_INTENDED_USE
  ,AIL.SHIP_TO_LOCATION_ID
  ,HR.LOCATION_CODE SHIP_TO_LOCATION_CODE
  ,AIL.PRODUCT_FISC_CLASSIFICATION
  ,AIL.USER_DEFINED_FISC_CLASS
  ,AIL.TRX_BUSINESS_CATEGORY
  ,AIL.PRODUCT_TYPE
  ,AIL.PRODUCT_CATEGORY
  ,AIL.ASSESSABLE_VALUE
  ,AIL.CONTROL_AMOUNT
  ,AIL.INCLUDED_TAX_AMOUNT
  ,AIL.TOTAL_REC_TAX_AMOUNT
  ,AIL.TOTAL_NREC_TAX_AMOUNT
  ,AIL.TAX_REGIME_CODE
  ,AIL.TAX
  ,AIL.TAX_STATUS_CODE
  ,AIL.TAX_RATE_CODE
  ,AIL.TAX_RATE_ID
  ,AIL.TAX_RATE
  ,AIL.TAX_JURISDICTION_CODE
  ,AIL.SUMMARY_TAX_LINE_ID
  ,AIL.TAX_ALREADY_CALCULATED_FLAG
  ,AIL.TAX_CLASSIFICATION_CODE
  ,AIL.APPLICATION_ID
  ,AIL.PRODUCT_TABLE
  ,AIL.REFERENCE_KEY1
  ,AIL.REFERENCE_KEY2
  ,AIL.REFERENCE_KEY3
  ,AIL.REFERENCE_KEY4
  ,AIL.REFERENCE_KEY5
  ,DECODE(AIL.PO_HEADER_ID,NULL,AIL.PURCHASING_CATEGORY_ID,
  	  PL.CATEGORY_ID)
  ,AIL.COST_FACTOR_ID
  ,PPET.NAME COST_FACTOR_NAME
  ,AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount(AIL.Invoice_Id,AIL.Line_Number) RECOUPED_AMOUNT
  ,DECODE(ai.invoice_type_lookup_code, 'PREPAYMENT', NULL, pl.recoupment_rate) RECOUPMENT_RATE
  ,AIL.RETAINED_AMOUNT			RETAINED_AMOUNT
  ,AIL.RETAINED_AMOUNT_REMAINING	RETAINED_AMOUNT_REMAINING
  ,AIL.RETAINED_INVOICE_ID		RETAINED_INVOICE_ID
  ,AIR.INVOICE_NUM			RETAINED_INVOICE_NUM
  ,AIL.RETAINED_LINE_NUMBER		RETAINED_LINE_NUMBER
  ,AIL.LINE_SELECTED_FOR_RELEASE_FLAG	LINE_SELECTED_FOR_RELEASE_FLAG
  ,AIL.RCV_SHIPMENT_LINE_ID		RCV_SHIPMENT_LINE_ID
  ,AIL.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID    /*bug 7439828*/
  ,AWT1.NAME PAY_AWT_GROUP_NAME             /*bug 7439828*/
FROM  AP_INVOICE_LINES_ALL         AIL
  , AP_INVOICES_ALL                AI
  , GL_SETS_OF_BOOKS               GSOB
  , AP_LOOKUP_CODES                ALC1
  , AP_LOOKUP_CODES                ALC2
  , AP_DISTRIBUTION_SETS_ALL       APID
  , PO_DISTRIBUTIONS_ALL           PD
  , PO_HEADERS_ALL                 PH
  , PO_LINES_ALL                   PL
  , AP_LOOKUP_CODES                ALC3  /* Amount Based Matching */
  , PO_LINE_LOCATIONS_ALL          PLL
  , PO_RELEASES_ALL                PR
  , RCV_TRANSACTIONS               RTXNS
  , RCV_SHIPMENT_HEADERS           RSH
  , RCV_SHIPMENT_LINES             RSL
  , HR_ORGANIZATION_UNITS          HOU
  , PA_PROJECTS_ALL                PAP
  , PA_TASKS_EXPEND_V              PAT  /*Bug 16391730*/
  , FA_BOOK_CONTROLS               FABC
  , AP_AWT_GROUPS                  AWT
  , AP_AWT_GROUPS                  AWT1 /*bug 7439828*/
  , AP_INVOICES_ALL                AIC  /*For Corrected Invoice Number*/
  , AP_INVOICES_ALL                AIII /*For Intrecompany Invoice Number*/
  , AP_INVOICES_ALL                AIP  /*For Prepayment Invoice Number*/
  , AP_INVOICES_ALL		   AIR  /*For Retained Invoice Number*/
  , HR_LOCATIONS		   HR
  , PON_PRICE_ELEMENT_TYPES_VL     PPET
WHERE  AIL.invoice_id = AI.invoice_id
AND    AIL.set_of_books_id = GSOB.set_of_books_id
AND    ALC1.lookup_type (+)   = 'INVOICE LINE TYPE'
AND    ALC1.lookup_code (+)   = AIL.line_type_lookup_code
AND    ALC2.lookup_type (+)   = 'AP_WFAPPROVAL_STATUS'
AND    ALC2.lookup_code (+)   = AIL.wfapproval_status
AND    AIL.distribution_set_id   =  APID.distribution_set_id(+)
AND    AIL.po_distribution_id = PD.po_distribution_id(+)
AND    AIL.po_header_id = PH.po_header_id(+)
AND    AIL.po_line_location_id = PLL.line_location_id(+)
AND    AIL.po_line_id = PL.po_line_id(+)
AND    ALC3.lookup_type(+) = 'MATCHING BASIS'  /* Amount Based Matching */
AND    ALC3.lookup_code(+) = PLL.Matching_Basis /* Amount Based Matching */
AND    AIL.po_release_id = PR.po_release_id(+)
AND    AIL.rcv_transaction_id = RTXNS.transaction_id(+)
AND    RSL.shipment_header_id = RSH.shipment_header_id(+)
AND    RSL.shipment_line_id(+) = AIL.rcv_shipment_line_id
AND    AIL.project_id = PAP.project_id(+)
AND    AIL.task_id = PAT.task_id(+)
AND    AIL.expenditure_organization_id = HOU.organization_id(+)
AND    AIL.asset_book_type_code = FABC.book_type_code(+)
AND    AIL.awt_group_id = AWT.group_id(+)
AND    AIL.PAY_AWT_GROUP_ID = AWT1.group_id(+) /*bug 7439828*/
AND    AIL.corrected_inv_id = AIC.invoice_id(+)
AND    AIL.pa_cc_ar_invoice_id = AIII.invoice_id(+)
AND    AIL.prepay_invoice_id = AIP.invoice_id(+)
AND    AIL.retained_invoice_id = AIR.invoice_id(+)
AND    AIL.ship_to_location_id = HR.location_id(+)
AND    AIL.cost_factor_id = PPET.price_element_type_id(+)
--and  AIL.invoice_id = 45043
     and PH.SEGMENT1 = '20000141'
     and PH.ORG_ID =94

另一个sql 是专门查找前端采购订单的信息：

oracle-EBS-PL/sql AP 发票中匹配的po明细