Account Payables
Concepts of Payables: Defining Bank accounts (supplier bank a/c and our Internal bank a/c) Defining Supplier Defining payment terms and payment formats Defining Distribution Sets Raise Individual invoices; batch Invoices, Recurring and Interest Invoices Payments Batch payments Individual payments Pre-Payments After all a run report called TRANSFER TO GL Holds: Holds are the advanced feature of applications, which restrict the payments of invoices. We have two types of holds. 1. System holds 2.User-Defined Holds Invoice: Invoice is a legal document, which we receive from the supplier that consists of amounts owed to the supplier for purchase goods or services. Types of Invoices: 1. Standard Invoices 2. Expense Invoices 3. Gate Way Invoice or Recurring Invoice 4. Withholding invoices 5. Debit Memo 6. Credit Memo 7. Pre-payment Invoices 8. Quick Match Invoices 9. PO Default Invoices 10. Mixed or Miscellaneous Invoices Standard Invoices: Issued by the supplier and Batch Invoice: it is a group of invoices Gate Way Invoice or Recurring Invoice: a feature that lets you create invoice for an expence that occurs regularly and not usually invoiced. Examples: Rent or lease invoices Interest invoices: an invoice that oracle payables create to pay interest on post-due invoice Debit invoices: An invoice we generate to send to a supplier representing a credit amount that the supplier owes to us. A debit invoice can represent a quantity credit or a price reduction. Credit Invoices: An invoice we receive from a supplier representing a credit amount that the supplier owes to us. A credit invoice can represent a quantity credit or a price reduction. Pre-payment Invoices: this is the advance payment given against the invoice given by the supplier. AP_AUD_AUDITORS is a Audit-specific table that stores information about auditors. The data in this table controls which users are valid auditors. The table is shipped preseeded with a fallback auditor. The fallback auditor is used when no other auditor is available and has auditor_id -1. The primary key for this table is AUDITOR_ID. AP_AWT_TAX_RATES_ALL AP_HOLDS_ALL Important columns: 1. INVOICE_ID : Invoice identifier 2. LINE_LOCATION_ID : Purchase order line location identifier 3. HOLD_LOOKUP_CODE : Name of hold code 4. HELD_BY : User that placed hold on invoice 5. HOLD_DATE : Date user placed hold on invoice 6. HOLD_REASON : Reason for hold being placed on invoice 7. RELEASE_LOOKUP_CODE : Name of release code 8. RELEASE_REASON : Reason for release being placed on invoice 9. LINE_NUMBER : Invoice line number of the applied hold 10. HOLD_ID : Hold identifier AP Detail Hold information of the Invoice select * from (SELECT 'Holds - Source1' AS SOURCE, Summary Hold information based on the Invoice Number When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the Summary level. ACCOUNT PAYABLES TOTAL TABLES INFORMATION
Important tables in AP modules
AP_ACCOUNTING_EVENTS_ALL
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
AP_AGING_PERIODS
AP_AGING_PERIOD_LINES
AP_AUD_AUDITORS
AP_AWT_BUCKETS_ALLAP_AWT_GROUPS
AP_AWT_TAX_RATES_ALL contains tax rates for Withholding tax type tax codes. Oracle Payables predefines the Standard and Penalty tax rate types for withholding taxes. In addition, Certificate rate types are assigned to a specific supplier site and Exception rate types are assigned to a specific invoice. START_AMOUNT and END_AMOUNT columns are only used if the parent withholding tax uses amount ranges.
AP_AWT_TEMP_DISTRIBUTIONS_ALL
AP_AWT_TEMP_DISTRIBUTIONS_ALL contains temporary withholding tax type invoice distributions for projected withholding tax or unconfirmed payment batches.
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_ALL contains information about your bank accounts. You need one row for each bank account you define. Each bank account must be affiliated with one bank branch. When you initiate an automatic payment batch, enter a manual check, or create a Quick payment, you can select a bank account that you define in this table.
AP_BANK_ACCOUNT_USES_ALL
AP_BANK_ACCOUNT_USES_ALL stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.
AP_BANK_BRANCHES
AP_BANK_BRANCHES contains information about the bank branches you define when you set up your banks. You need one row for each bank branch you use. One bank branch may have multiple bank accounts.
AP_BANK_CHARGES
AP_BANK_CHARGES stores header information you enter in the Bank Charges window in Oracle Payables or Oracle Receivables. You need one row for each bank combination you define in the Bank Charges window. TRANSFER_PRIORITY is NULL for a record entered in Oracle Receivables, and NOT NULL for a record entered in Oracle Payables.
AP_BANK_CHARGE_LINES
AP_BANK_CHARGE_LINES stores ranges for bank charges and tolerance limits. The range consists of TRANS_AMOUNT_FROM and TRANS_AMOUNT_TO. The record will be uniquely identified by BANK_CHARGE_ID,TRANS_AMOUNT_FROM, and START_DATE.
AP_BANK_TRANSMISSIONS
AP_BANK_TRANSMISSIONS stores files you send and receive when you use the Automatic Bank Transmission feature. This table also stores information about each transmitted file. This table has one row for each file sent or received.
AP_BATCHES_ALL
AP_BATCHES_ALL contains summary information about invoices you enter in batches if you enable the Batch Control Payables option. There is one row for each batch of invoices you enter. If you enable Batch Control, each invoice must correspond to a record in this table. Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.
AP_CHECKRUN_CONC_PROCESSES_ALL
AP_CHECKRUN_CONC_PROCESSES_ALL is a temporary table that contains records for payment batches with concurrent requests in process. Your Oracle Payables application creates a row in the table for a payment batch when it submits the concurrent request for the batch. When you select a payment batch to modify, format, confirm, cancel, or restart, the system checks if there is a record in the table for the batch. If a record exists, the system checks the status of the concurrent request. If the status of the request is Pending, Running, or Inactive (indicating that the request may start running at some later time), the system does not allow you to perform any action on the payment batch. If the concurrent request is completed, the system allows you to proceed and deletes the existing record for the payment batch.
AP_CHECKRUN_CONFIRMATIONS_ALL
AP_CHECKRUN_CONFIRMATIONS_ALL is a temporary table that a payment batch uses during the confirmation stage of a payment batch. There is one row for each payment status (e.g. PRINTED, SET UP, SPOILED) you use in a payment batch. Your Oracle Payables application deletes the information from this table when you complete a payment batch.
AP_CHECKS_ALL
AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers. You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier's bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a
foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.
AP_CHECK_FORMATS
AP_CHECK_FORMATS contains records for payment formats you can use to create payment documents. You need one row for each format you use to create payments (for example, checks) to suppliers. The table AP_PAYMENT_PROGRAMS stores payment program names that you can use to define payment formats. Each row in this table references AP_PAYMENT_PROGRAMS either two or three times, once in CREATE_PAYMENTS_PROGRAM_ID, once in CONFIRM_PAYMENTS_PROGRAM_ID, and once in REMITTANCE_ADVICE_PROGRAM_ID for formats with a separate remittance advice program.
AP_CHECK_INTEGERS
AP_CHECK_INTEGERS is a temporary table that the Missing Document Report uses to store document numbers while it searches AP_CHECKS_ALL for gaps in the sequence of document numbers.
AP_CHECK_STOCKS_ALL
AP_CHECK_STOCKS_ALL stores information about payment documents you defined for bank accounts. You need one row for each payment document you use to create payments for a supplier. Each record in this table must be associated with a bank account. Each bank account corresponds with zero or more rows in this table. When you initiate a payment batch, record a manual payment, or create a Quick payment, you can select a payment document that you defined in this table. For a payment document you use to create automatic payments, DISBURSEMENT_TYPE_LOOKUP_CODE must be 'COMPUTER GENERATED' or 'COMBINED.' For manual payments, it must be 'RECORDED' or 'COMBINED.' Your Oracle Payables application updates the LAST_DOCUMENT_NUM when you create payments in an automatic payment batch, enter a manual payment, or create a Quick payment.
AP_DISTRIBUTION_SETS_ALL
AP_DISTRIBUTION_SETS_ALL contains information about Distribution Sets you define to distribute invoices automatically. You need one row for each Distribution Set you use in your Oracle Payables application. When you enter an invoice, you can select a Distribution Set that you define in this table. When you select a Distribution Set for an invoice, your Oracle Payables application automatically creates invoice distribution lines according to the lines you define for the Distribution Set you select. A Distribution Set must have one or more Distribution Set lines.
AP_DISTRIBUTION_SET_LINES_ALL
AP_DISTRIBUTION_SET_LINES_ALL contains detailed information about individual distribution lines you define for a Distribution Set. You need one row for each distribution line for a Distribution Set. When you select a Distribution Set for an invoice, your Oracle Payables application uses this information to create invoice distribution lines.A Distribution Set line must be affiliated with a single Distribution Set. This table corresponds to the Distribution Sets window. This table has no primary key, since your Oracle Payables application accesses its records exclusively in sets according to DISTRIBUTION_SET_ID.
AP_DOC_SEQUENCE_AUDIT
AP_DOC_SEQUENCES_AUDIT contains audit information for document sequence numbers assigned to invoices and payments. Document sequence numbers are used to create unique, sequential, voucher numbers for referencing invoices and payments.Each document sequence is defined with a range of numbers. A document sequence is assigned to a document category which, in turn, is assigned to a document (invoice or payment) during creation.When a number for a document sequence is assigned to an invoice or payment, your Oracle Payables application creates a row in this table for the number. Your Oracle Payables application uses the records in this table to verify the availability of a number for a particular document sequence before allowing the number to be assigned to a document.
AP_DUPLICATE_VENDORS_ALL
AP_DUPLICATE_VENDORS_ALL stores information about suppliers that Supplier Merge updates. The Supplier Merge window inserts information into this table and then the Supplier Merge concurrent process uses this information to merge suppliers
AP_ENCUMBRANCE_LINES_ALL
AP_ENCUMBRANCE_LINES_ALL stores all encumbrance entries created by Oracle Payables. It contains one line per encumbrance entry. An encumbrance line could be created to encumber a charge, QV, IPV, or ERV, to unencumber an entry created by PO or to reverse an encumbrance entry once the charge, QV, IPV, or ERV becomes an actual. This table is used to transfer encumbrance over to GL, to view encumbrance entries or to report on them. It is not updatable through the application.
AP_EXPENSE_REPORTS_ALL
AP_EXPENSE_REPORTS contains information about expense report templates you define for entering expense reports. When you enter an expense report, you can use a list of values to select the expense report template that you defined in this table.
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_HEADERS_ALL stores header information about the expense reports entered in Oracle Payables and Oracle Self-Service Expenses. There is one row for each expense report you enter. Your Oracle Payables application uses this information to create invoice header information when you transfer expense reports to invoice tables. Transfer expense reports to invoice tables using the Expense Report Import program. Your Oracle Payables application places the invoice ID in the VOUCHNO column when you transfer an expense report. To import invoices from an external source, Oracle recommends that you use the new Payables Open Interface tables.
AP_EXPENSE_REPORT_LINES_ALL
AP_EXPENSE_REPORT_LINES_ALL stores detailed information about expense items for individual expense reports you enter. Your Oracle Payables application uses this information to create invoice distributions when you import expense reports using the Expense Report Import program.
AP_EXPENSE_REPORT_PARAMS_ALL
AP_EXPENSE_REPORT_PARAMS_ALL stores accounting distribution information about expense report types you define. You need one row for each line you want in the template for an expense report template that you use to enter employee expense reports.
AP_HISTORY_CHECKS_ALL
AP_HISTORY_CHECKS_ALL contains summary information on invoice payments your Oracle Payables application purges based on purge criteria you enter when you submit a purge. Your Oracle Payables application inserts summary payment information when you confirm a purge process. Your Oracle Payables application stores the invoice information in AP_HISTORY_INVOICES_ALL and stores the relationships in AP_HISTORY_INVOICE_PAYMENTS_ALL.
AP_HISTORY_INVOICES_ALL
AP_HISTORY_INVOICES_ALL stores summary information of the invoices your Oracle Payables application purges based on purge criteria you enter when you submit a purge. Your Oracle Payables application inserts summary invoice information in this table when you confirm a purge process. Your Oracle Payables application stores related payment information in AP_HISTORY_CHECKS and stores the relationships in AP_HISTORY_INV_PAYMENTS.
AP_HISTORY_INV_PAYMENTS_ALL
AP_HISTORY_INVOICE_PAYMENTS_ALL stores the relationship between invoices and payments that your Oracle Payables application purges based on purge criteria you enter when you submit a purge. Your Oracle Payables application inserts summary payment and invoice information in this table when you confirm a purge process. There is one row for each payment you make for an invoice. Summary information for each payment and each invoice is stored in AP_HISTORY_CHECKS and AP_HISTORY_INVOICES
AP_HOLDS_ALL
AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Payables Invoice Validation), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID, and HOLD_LOOKUP_CODE.
AP_HOLD_CODES
AP_HOLD_CODES contains information about hold codes and release codes that you or your Oracle Payables application can place on an invoice. There is one row for each hold or release code. When you enter or validate an invoice, you can select a hold code that you defined in this table. Your Oracle Payables application also uses these hold codes when it places matching and tax variance holds during Invoice Validation.Your Oracle Payables application stores translations of HOLD_LOOKUP_CODES in the AP_LOOKUP_CODES table.
AP_INCOME_TAX_REGIONS
AP_INCOME_TAX_REGIONS contains information about the tax regions you use to record payment information for 1099 suppliers when your organization participates in the Combined Filing Program. Each tax region participating in the Combined Filing Program is assigned a region code, reporting limit, and a reporting limit method which is used to determine if the payments to a 1099 supplier exceed the region's reporting limit.When you enter a site for a 1099 supplier, you enter a tax region from this table in the site address. Your Oracle Payables application assigns either this tax region or a default tax region you define at the system level to each invoice distribution line for the supplier site.Your Oracle Payables application predefines the tax regions for all U.S. States, the District of Columbia, and some U.S. Territories. Payables has also predefined the region codes for those tax regions that are participating in the Internal Revenue Service's Combined Filing Program. You can define additional tax regions or you can enter region codes for existing regions.
AP_INCOME_TAX_TYPES
AP_INCOME_TAX_TYPES stores the 1099-MISC types that you can assign to a supplier for 1099 reporting. There is one row for each type you use to identify a supplier as a 1099
supplier. When you enter a supplier, you can select a 1099-MISC type if you define the supplier as Federal Reportable.
AP_INTEREST_PERIODS
AP_INTEREST_PERIODS contains information about interest rates and periods that Payables uses to create invoices to pay interest owed on overdue invoices. This interest is calculated in accordance with the U.S. Prompt Payment Act.There is one row for each period for which interest accrues on an invoice.
AP_INTERFACE_CONTROLS
AP_INTERFACE_CONTROLS is a temporary table that holds control information about segregated data in the AP_INVOICES_INTERFACE table during the Payables Open
Interface Import. The table ensures that each import must be unique with respect to the combination of SOURCE and GROUP_ID. This allows the submission of multiple imports at the same time. Your Oracle Payables application deletes the information from this table when you complete an import.
AP_INTERFACE_REJECTIONS
AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import. If you use Oracle e-Commerce Gateway, you can pass information from this table to your suppliers by submitting the Payables Open Interface Outbound Advice for rejected data. You can purge data in this table by using the Payables Open Interface Purge.
AP_INVOICES_ALL
AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have one or more scheduled payments. An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that it calculates on invoices that are overdue. Your Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.This table corresponds to the Invoices window.
AP_INVOICES_INTERFACE
AP_INVOICES_INTERFACE stores header information about invoices that you create or load for import. Invoice data comes from sources including: EDI invoices from your suppliers that you load through Oracle e-Commerce Gateway, supplier invoices that you transfer through the Oracle XML Gateway, invoices that you load using Oracle SQL*Loader, lease invoices from Oracle Property Manager, lease payments from Oracle Assets, credit card transaction data that you load using the Credit Card Invoice Interface Summary, and invoices that you enter through the Invoice Gateway. There is one row for each invoice you import. Your Oracle Payables application uses this information to create invoice header information when you submit the Payables Open Interface program. AP_INVOICES_INTERFACE contains a unique invoice ID which is used to interface with AP_INVOICES_ALL.Please refer to the appendix of the Oracle Payables User Guide or online help for more detailed information on Open Interface table columns, including import validation and destination columns.
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated with an invoice. An invoice can have multiple distribution lines.
Your Oracle Payables application automatically creates rows in this table when: 1) you choose a distribution set at the invoice level 2) you import expense reports 3) you match an invoice to a purchase order or receipt; it uses information from the matched purchase order or receipt 4) you import invoices via the Open Interface Import process 5) you select to automatically calculate tax 6) you select to automatically do withholding.
Each invoice distribution line has its own accounting date. When you account for an invoice, your Oracle Payables application creates accounting events, accounting entry headers and accounting entry lines for those distribution lines that have accounting dates included in the selected accounting date range for the Payables Accounting Process. The accounting entries can then be transferred over to General Ledger by running the Transfer to General Ledger process which creates journal entries. Values for POSTED_FLAG may be Y for accounted distributions or N for distributions that have not been accounted. Values for ACCRUAL_POSTED_FLAG may be Y if distribution has been accounted and system is setup for accrual basis accounting or N if either distribution has not been accounted or accrual basis accounting is not used. Values for CASH_POSTED_FLAG may be Y if distribution has been accounted and system is set up for cash basis accounting, N if either distribution has not been accounted or system is not setup for cash basis accounting or P if distribution has been partially accounted in the cash set of books. The MATCH_STATUS_FLAG indicates the validationl status for the distribution. Values for the MATCH_STATUS_FLAG can be null or N for invoice distributions that Invoice Validation has not tested or T for distributions that have been tested or A for distributions that have been tested and validated.Invoice distributions may be interfaced over/from Oracle Assets or Oracle Projects. Your Oracle Payables application sets the ASSETS_ADDITION_FLAG to U for distributions not tested by Oracle Assets; Oracle Assets then adjusts this flag after it tests a distribution for assignment as an asset. To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project-related invoice distribution to Oracle Projects before you can interface it to Oracle Assets. If the project-related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project-related, with the PA_ADDITION_FLAG set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project-related supplier invoice distribution lines and expense report distribution lines. For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project-related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Pro
AP_INVOICE_KEY_IND_ALL
AP_INVOICE_KEY_IND stores compiled invoice information for the Key Indicators Report. There is one row for each period for which you report on invoice key indicators. The information in this table is denormalized for reporting purposes.This table has no foreign keys.
AP_INVOICE_LINES_INTERFACE
AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution. For example, if you enter a Tax line in this table and prorate it across multiple Item lines, during Open Interface Import the system may create multiple Tax invoice distributions based on the single Tax line in this table
AP_INVOICE_PAYMENTS_ALL
AP_INVOICE_PAYMENTS_ALL contains records of invoice payments that you made to suppliers. There is one row for each payment you make for each invoice. There is one payment and one invoice for each payment in this table. Your Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual Payment, or process a Quick payment. When you void a payment, your Oracle Payables application inserts an additional payment line that is the negative of the original payment line. Values for POSTED_FLAG may be 'Y' for accounted payments or 'N' for unaccounted payments. Values for ACCRUAL_POSTED_FLAG may be 'Y' for accounted payments or 'N' for unaccounted payments under accrual basis accounting; values for SH_POSTED_FLAG
may be 'Y' for accounted payments or 'N' for unaccounted payments under cash basis accounting.
AP_INVOICE_RELATIONSHIPS
AP_INVOICE_RELATIONSHIPS relates records in AP_INVOICES_ALL with one another to match Interest invoices with corresponding overdue invoices. If you use automatic interest calculation, Payables creates Interest invoices to pay interest on overdue invoices. This interest is calculated in accordance with the U.S. Prompt Payment Act.
AP_INV_APRVL_HIST_ALL
AP_INV_APRVL_HIST_ALL contains the approval and rejection history of each invoice that passes through the Invoice Approval Workflow process. The process inserts a record for each approver assigned to review an invoice. This table corresponds to the Invoice Approval History window.
AP_INV_SELECTION_CRITERIA_ALL
AP_INVOICE_SELECTION_CRITERIA_ALL stores the criteria that a payment batch uses to select invoices for payment. Your Oracle Payables application creates one row each time you initiate a payment batch. Your Oracle Payables application uses this information to guide AutoSelect in selecting invoices for payment.
AP_LIABILITY_BALANCE
AP_LIABILITY_BALANCE contains denormalized information about liability accounting entry lines associated with invoices and payments that have been transferred to GL. The Accounts Payable Trial Balance report uses this information to report on outstanding invoice liability.
AP_MATCHED_RECT_ADJ_ALL
A new record is added to this table each time a receipt that has been matched to by Payables is adjusted. Each record contains the original receipt transaction identifier and the adjusted receipt transaction identifier along with the date it was adjusted. The data in this table will be used for the Matched and Modified Receipts Report. This table records only 'RETURN' and 'ADJUST' type transactions in Purchasing.
AP_MC_CHECKS
AP_MC_CHECKS is a Multiple Reporting Currencies table that stores reporting currency information for each payment in Oracle Payables. There is an optional one to many relationship between AP_CHECKS_ALL and this table. For each payment in the AP_CHECKS_ALL table, there can be several invoices in the AP_MC_CHECKS table with the same CHECK_ID value, but different values for SET_OF_BOOKS_ID. The number of invoices stored in the AP_MC_CHECKS table having the same CHECK_ID will correspond with the number of Reporting Sets of Books that have been defined.
AP_MC_INVOICES
AP_MC_INVOICES is a Multiple Reporting Currencies table that stores reporting currency information for each invoice in Oracle Payables. There is an optional one to many relationship between AP_INVOICES_ALL and this table. For each invoice in the AP_INVOICES_ALL table, there can be several invoices in the AP_MC_INVOICES table with the same INVOICE_ID value, but different values for SET_OF_BOOKS_ID. The number of invoices stored in the AP_MC_INVOICES table having the same INVOICE_ID will correspond with the number of Reporting Sets of Books that have been defined.
AP_MC_INVOICE_DISTS
AP_MC_INVOICE_DISTS is a Multiple Reporting Currencies table that stores reporting currency information for each invoice distribution in Oracle Payables. There is an optional one to many relationship between AP_INVOICE_DISTRIBUTIONS_ALL and this table. For each invoice distribution in AP_INVOICE_DISTRIBUTIONS_ALL, there can be several Invoice distributions in the AP_MC_INVOICE_DISTS table with the same value for INVOICE_ID and DISTRIBUTION_LINE_NUMBER, but different values for SET_OF_BOOKS_ID. The number of invoice distributions stored in the AP_MC_INVOICE_DISTS table having the same INVOICE_ID and DISTRIBUTION_LINE_NUMBER will correspond with the number of Reporting Sets of Books that have been defined. There is a mandatory one to many relationship between
the AP_MC_INVOICES table and the AP_MC_INVOICE_DISTS table.
AP_MC_INVOICE_PAYMENTS
AP_MC_INVOICE_PAYMENTS is a Multiple Reporting Currencies table that stores reporting currency information for each payment in Oracle Payables. There is an optional one to many relationship between AP_INVOICE_PAYMENTS_ALL and this table. For each invoice in the AP_INVOICE_PAYMENTS_ALL table, there can be several payments in the
AP_MC_INVOICE_PAYMENTS table with the same INVOICE_PAYMENT_ID value, but different values for SET_OF_BOOKS_ID. The number of payments stored in the AP_MC_INVOICE_PAYMENTS table having the same INVOICE_PAYMENT_ID will correspond with the number of Reporting Sets of Books that have been defined
AP_OTHER_PERIODS
AP_OTHER_PERIODS contains information about the time periods you define for use in recurring invoices, withholding taxes, key indicators and payment terms. Each row includes a start date and an end date to define the length of the period. There is one row for each period you use to create key indicators, withholding tax payments, recurring invoices or payment schedules. Your Oracle Payables application uses this information to determine if periods are available when you define and create recurring invoices or submit a Key Indicators report.
AP_OTHER_PERIOD_TYPES
AP_OTHER_PERIOD_TYPES stores the period type you define and use to create recurring invoice, automatic withholding tax, key indicators and payment terms periods. You need one row for each type of period you use to create recurring invoice, automatic withholding tax, key indicators or payment terms periods. You must enter a row in this table before you can create rows in AP_OTHER_PERIODS. Examples of period types are Monthly, Semi-Annual, and Quarterly. Values for MODULE may be 'KEY INDICATORS', 'RECURRING PAYMENTS', 'AWT' or 'PAYMENT TERMS'.
AP_PAYMENT_HISTORY_ALL
AP_PAYMENT_HISTORY_ALL stores the clearing/unclearing history for payments. It also stores the maturity history for future dated payments. The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable. Any time a payment is cleared or uncleared, a row is inserted into this table for the payment. The values for TRANSACTION_TYPE can be PAYMENT MATURITY, PAYMENT CLEARING, or PAYMENT UNCLEARING. Each row in this table also has the accounting status for the maturity, clearing or unclearing event.
AP_PAYMENT_KEY_IND_ALL
AP_PAYMENT_KEY_IND stores compiled payment information for the Key Indicators Report. There is one row for each period for which you report on payment key indicators. The information in this table is denormalized for reporting purposes
AP_PAYMENT_PROGRAMS
AP_PAYMENT_PROGRAMS stores information about payment programs that you use to define payment formats. You need one row for each payment program you use. Your Oracle Payables application uses this information to use the proper payment programs when you use a payment format to create payments. Values in PROGRAM_NAME correspond to names of Pro*C, SQL*Plus, or Oracle Reports programs. Rows in AP_CHECK_FORMATS reference rows in this table. This table corresponds to the Automatic Payment Programs window. This table has no foreign keys.
AP_PAYMENT_SCHEDULES_ALL
AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be 'Y' to place a hold on the scheduled payment, or 'N' not to do so. Values for PAYMENT_STATUS_FLAG may be 'Y' for fully paid payment schedules, 'N' for unpaid scheduled payments, or 'P' for partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.This table corresponds to the Scheduled Payments window
AP_PBATCH_SETS_ALL
AP_PBATCH_SETS_ALL stores the payment batch set definitions. You need one row for each payment batch set that you want to define. You must enter a row in this table before you can enter rows in AP_PBATCH_SET_LINES_ALL. This table corresponds to the single-row region in Payment Batch Sets window.
AP_PBATCH_SET_LINES_ALL
AP_PBATCH_SET_LINES_ALL stores the payment batch set lines that make up a payment batch set. You need one row for each payment batch that you would like to be created .when you submit the payment batch set to which the payment batch set lines belong. Payment batch set lines belonging to a payment batch set may use the same or different bank accounts. However, they must use different payment documents. The values from each row in this table are used to insert a row in AP_INV_SELECTION_CRITERIA_ALL. When the payment batch set is submitted. This table corresponds to the multi-row region in Payment Batch Sets window.
AP_PURGE_INVOICE_LIST
AP_PURGE_INVOICE_LIST is a temporary table used to store the INVOICE_IDs during the invoice purges process. There is one row for each invoice your Oracle Payables application selects to purge based on invoice purge criteria you enter. Your Oracle Payables application clears the records in this table when you confirm or abort a purge process. Your Oracle Payables application stores summary information on purged invoices and payments in AP_HISTORY_INVOICES_ALL and AP_HISTORY_CHECKS_ALL. Your Oracle Payables application stores the status of the purge process and statistical information on records it purges in the FINANCIALS_PURGES table.
AP_RECURRING_PAYMENTS_ALL
AP_RECURRING_PAYMENTS_ALL contains information about recurring invoices you define. You need one row for each set of recurring invoices you arrange with a supplier. Your Oracle Payables application uses this information to help you create recurring invoices without duplicating data entry. Values for PAID_FLAG1 and PAID_FLAG2 may be 'Y' for Yes or 'N' for No. This table corresponds to the Recurring Invoices window
AP_REPORTING_ENTITIES_ALL
AP_REPORTING_ENTITIES_ALL contains information about the reporting entities you define for 1099 reporting. You must define at least one reporting entity for your company or agency; however, you can define as many reporting entities as you require to satisfy your multi-organization reporting needs. Each tax reporting entity you define must have a unique Tax Identification Number. When you submit your 1099 reports, you submit the reports once for each tax reporting entity you define.
AP_REPORTING_ENTITY_LINES_ALL
AP_REPORTING_ENTITY_LINES_ALL holds the line information for the tax reporting entities you define. Each tax reporting entity must have at least one line that contains the balancing segment value for the organization for which you perform your 1099 reporting. If you define more than one organization for your company or agency, you can define tax reporting entities to handle your multi-organization 1099 reporting. You use different balancing segment values to represent the different organizations in your company or organization.You can define a reporting entity with a single balancing segment line for each organization or you can define a reporting entity with multiple lines to combine 1099 reporting for your organizations. For multi-organization reporting entities, you define a different balancing segment value for each line; however, you cannot assign the same balancing segment value to more than one reporting entity. This table corresponds to the Balancing Segment Values region of the Reporting Entity window.
AP_REPORTING_ENTITY_LINES_ALL
AP_REPORTING_ENTITY_LINES_ALL holds the line information for the tax reporting entities you define. Each tax reporting entity must have at least one line that contains the balancing segment value for the organization for which you perform your 1099 reporting.If you define more than one organization for your company or agency, you can define tax-reporting entities to handle your multi-organization 1099 reporting. You use different balancing segment values to represent the different organizations in your company or organization. You can define a reporting entity with a single balancing segment line for each organization or you can define a reporting entity with multiple lines to combine 1099 reporting for your organizations. For multi-organization reporting entities, you define a different balancing segment value for each line; however, you cannot assign the same balancing segment value to more than one reporting entity. This table corresponds to the Balancing Segment Values region of the Reporting Entity window.
AP_SELECTED_INVOICES_ALL
AP_SELECTED_INVOICES_ALL is a temporary table that stores information about invoices selected for payment in a payment batch. Your Oracle Payables application inserts into this table after you initiate a payment batch. There will be one row for each invoice that Payables selects for payment in the current payment batch. When you build payments in a payment batch, your Oracle Payables application uses information in this table to create rows in AP_SELECTED_INVOICE_CHECKS. Information from this table appears in the Modify Payment Batch window.
AP_SELECTED_INVOICE_CHECKS_ALL
AP_SELECTED_INVOICE_CHECKS_ALL is a temporary table that stores payment information during a payment batch. Your Oracle Payables application inserts into this table when you build payments in a payment batch. There will be one row for each payment issued during the current payment batch. When you confirm a payment batch, your Oracle Payables application inserts these payments into AP_CHECKS_ALL and creates a payment file. Within a payment batch, SELECTED_CHECK_ID in this table joins with PRINT_SELECTED_CHECK_ID and PAY_SELECTED_CHECK_ID in AP_SELECTED_INVOICES to associate a selected invoice with its payment.
AP_SUPPLIER_BALANCE_ITF
This is an interface table that stores all the records for the submission of the AP Supplier Open Balance Letter report, as per the parameters specified by the user during the report submission. AP_SUPPLIER_BALANCE_ITF is a denormalized table used for storing the Organization Name, Address, Supplier and Supplier Site level information. Each row in this table stores invoice amount, payment amount, discount taken, discount available, original prepayment amount, prepayment amount applied to an invoice, prepayment amount remaining and the prepayment amount applied. Using this information, the supplier open balance letter calculates the supplier open balance and publishes it in the form of a letter.
AP_SYSTEM_PARAMETERS_ALL
AP_SYSTEM_PARAMETERS_ALL contains the parameters and defaults you define for operating your Oracle Payables application system. This table contains information such as your set of books, your functional currency and your default bank account and payment terms. Your Oracle Payables application also uses this information to determine default values that cascade down to supplier and invoice entry. This table corresponds to the Payables Options window.There is only one row in this table. There is no primary key for this table.
AP_TAX_CODES_ALL
AP_TAX_CODES_ALL contains information about the tax codes you define. You need one row for each tax code you want to use when entering invoices. When you enter invoices, you can use a list of values to select a tax code defined in this table. Your Oracle Payables application also uses TAX_RATE to calculate use tax when you enter a taxable amount for an invoice.
AP_TAX_RECVRY_RATES_ALL
AP_TAX_RECVRY_RATES_ALL is used to define tax recovery rates for each recovery rule based on the accounting flex field, effective date range and/or supplier classification. A tax recovery rule may have one or more tax recovery rates. A rate is defined for an accounting flex field and an effective date range. Using the tax code, the recovery rule, and the accounting flex field range information on the distributions of purchasing documents and supplier invoices, the appropriate tax recovery rate is automatically defaulted
AP_TAX_RECVRY_RULES_ALL
AP_TAX_RECVRY_RULES_ALL is used to define tax recovery rules. A user can set up tax recovery rules to determine the recoverable percentage of tax. A tax rule can be assigned to a tax code and is used as the criteria to default a tax recovery rate on Requisitions, Purchase Orders and Releases, and Supplier Invoices.
AP_TEMP_APPROVAL_ALL
AP_TEMP_APPROVAL_ALL is a temporary table used by your Oracle Payables application to store hold information about invoices processed during Invoice Validation. When you submit Invoice Validation, any holds applied or released are inserted into this table.Your Oracle Payables application then selects from these records to produce the Invoice Validation Report.
AP_TERMS_LINES
AP_TERMS_LINES stores detail information about payment terms you define. You need one row for each scheduled payment that you want your Oracle Payables application to create for an invoice. Split terms have more than one row in this table. DAY_OF_MONTH columns pertain to proxima terms.
AP_TERMS_TL
AP_TERMS stores header information about payment terms you define. You need one row for each type of terms you use to create scheduled payments for invoices. When you enter suppliers or invoices, you can select payment terms you have defined in this table. Each terms must have one or more terms lines.
AP_TOLERANCES_ALL
AP_TOLERANCES_ALL contains the tolerance levels you set for matching and invoice variance testing. Your Oracle Payables application uses this information to determine during Invoice Validation whether to hold an invoice for exceeding amounts and quantities that you actually ordered or received, or for being taxed at significantly more or less than the proper tax rate.This table corresponds to the Invoice Tolerances window.There is only one row in this table.
AP_TRANSMISSIONS_SETUP
AP_TRANSMISSIONS_SETUP stores information required to use the Automatic Bank Transmission feature. Each bank can have one or more transmission codes, which provide details about transmitting files between your system and the bank. This table has one row for each transmission code you define
AP_TRIAL_BAL
Temporary table that holds outstanding invoice balances for the Trial Balance Report
AP_TRIAL_BALANCE
AP_TRIAL_BALANCE contains denormalized information about invoices and payments posted to the accrual set of books. The Accounts Payable Trial Balance report uses this information to report on outstanding invoice liability. There is one row for each set of invoice distributions or payments with a single accounting date that you post to the general ledger in a single run of the posting program.
FINANCIALS_PURGES
FINANCIALS_PURGES stores the criteria that your Oracle Payables application uses to select invoices to purge. Your Oracle Payables application inserts one row each time you submit the purge process. Your Oracle Payables application also stores statistical information on the number of records it deletes from each table when you confirm an invoice purge.
Values for STATUS may be 'INITIATE', 'PRINTED', 'DELETING', 'CONFIRM', 'ABORT', 'COMPLETED-ABORTED', and 'COMPLETED-PURGED'.This table corresponds to the Submit Purge form.
FINANCIALS_SYSTEM_PARAMS_ALL
FINANCIALS_SYSTEM_PARAMETERS_ALL contains options and defaults you share between your Oracle Payables application, and your Oracle Purchasing and Oracle Assets applications. You can define these options and defaults according to the way you run your business. This table corresponds to the Financials Options window. There is only one row in this table. There is no primary key for this table.
When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the detail level.
Note:- Normally this kind of Information will be required for the Top level management for the decision making.
api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num,
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, 'N', 'Y') AS defect,
poh.segment1 AS po_number,
por.release_num AS po_release_num,
pol.line_num AS po_line_num,
aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason,
aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) - TRUNC (aph.hold_date)) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer,
povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor,
rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty,
rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id (+) = pod.po_header_id --
AND pol.po_line_id (+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id (+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT 'Holds NotLinked To PO-Source2' AS SOURCE,
api.invoice_date AS invoice_date, api.invoice_num AS invoice_num,
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, 'N', 'Y') AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason, aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) - TRUNC (aph.hold_date)
) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NULL
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT 'NON Holds - Source 3' AS SOURCE, apii.invoice_date AS invoice_date,
apii.invoice_num AS invoice_num, pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount, 'N' AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, NULL AS hold_date,
NULL AS hold_lookup_code, NULL AS hold_reason, NULL AS release_date,
0 AS days_os, pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
apii.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all apii,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND apii.invoice_id = apd.invoice_id
AND apii.vendor_id = pov.vendor_id(+)
AND apii.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND apd.distribution_line_number NOT IN (
SELECT apd.distribution_line_number
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND api.invoice_id = apii.invoice_id)
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE)
where invoice_num='Your Invoice number'
select * from
(SELECT api.invoice_id, api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num, pov.vendor_id AS vendor_id,
pov.vendor_name AS supplier_name, apd.inv_lines AS total_inv_lines,
NVL (hold_tab_info.hold_inv_lines, 0) AS total_line_holds,
NVL (CEIL ((hold_tab_info.hold_inv_lines * 100) / DECODE(apd.inv_lines,0,1,apd.inv_lines)),
0
) AS percentage_line_hold,
DECODE (hold_tab_info.hold_inv_lines,
NULL, 'N',
0, 'N',
'Y'
) AS defect,
DECODE (hold_tab_info.hold_inv_lines,
NULL, 0,
0, 0,
1
) AS defect_count, 1 inv_count,
NVL (hold_count.hold_cnt, 0) AS total_inv_holds,
NVL (c.hold_os, 0) AS days_outstanding,
NVL (api.invoice_amount, 0) AS total_invoice_amount,
NVL (hold_tab_info.hold_amount, 0) AS total_hold_amount,
NVL (CEIL ((hold_tab_info.hold_amount * 100) / DECODE(api.invoice_amount,0,1,api.invoice_amount)),
0
) AS percentage_amount_hold
FROM APPS.ap_invoices_all api,
(SELECT invoice_id, COUNT (invoice_id) inv_lines
FROM APPS.ap_invoice_distributions_all
GROUP BY invoice_id) apd,
(SELECT invoice_id, COUNT (hold_lookup_code) hold_cnt
FROM APPS.ap_holds_all
WHERE 1 = 1 AND line_location_id IS NOT NULL
GROUP BY invoice_id) hold_count,
(SELECT invoice_id, COUNT (hold_tab.line_num) hold_inv_lines,
SUM (hold_tab.hold_amount) hold_amount
FROM (SELECT DISTINCT api.invoice_id invoice_id,
apd.distribution_line_number line_num,
apd.amount hold_amount
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NOT NULL) hold_tab
GROUP BY invoice_id) hold_tab_info,
(SELECT invoice_id, MAX (b.hold_os) hold_os
FROM (SELECT invoice_id,
DECODE (status_flag,
'R', ( TRUNC (NVL (last_update_date,
SYSDATE)
)
- TRUNC (hold_date)
),
(TRUNC (SYSDATE) - TRUNC (hold_date))
) hold_os
FROM APPS.ap_holds_all
WHERE line_location_id IS NOT NULL) b
GROUP BY invoice_id) c,
APPS.po_vendors pov
WHERE 1 = 1
AND hold_tab_info.invoice_id(+) = api.invoice_id
AND c.invoice_id(+) = api.invoice_id
AND api.invoice_id = apd.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND api.invoice_id = hold_count.invoice_id(+))
where invoice_num='Your Invoice number';
0 comments:
Post a Comment