Account Payables Tables

Account Payables Tables

1)Invoice Details.
a)Ap_invoices_all(INVOICE_ID)
You can see the approved Invoices.
b)Ap_invoice_Distributions_all(INVOICE_ID)
To get Distributed Invoices information.
2)Invoice Transactions
a)ap_ae_headers_all(AE_HEADER_ID)
b)ap_ae_lines_all
Stores Distributed Accounting information.
3)Payment Schedule Information
a)ap_payment_schedules_all(INVOICE_ID)
Stores Amount remaing information and schedule payments for an invoice.
b)ap_invoice_payments_all(INVOICE_ID,CHECK_ID)
After compleing invoice payment information stores here.
4) Check Information.
a)ap_checks_all(CHECK_ID)
if you done the payment via check this information stores here.
b)AP_CHECK_FORMATS(Check_format_id)
When you create the invoice ie assciated with accouting infomation that information stores in this table.
c)AP_MC_INVOICES(invoice_id,set_of_book_id)
Contains Multiple invoice Currency information as well as Exchange Information. 
d)AP_HOLDS_ALL
Holds invoice information you places
f)AP_CHRG_ALLOCATIONS_ALL
Used for AP links with the appropriate invoice distributins.
5)Approval information
a)AP_INV_APRVL_HIST_ALL(Approval history id,invoice_id)
Invoice approval information
b)AP_HISTORY_INVOICES_ALL(Invoice_id,VENDOR_iD)
All invoice history information stores here
c)AP_INVOICE_TRANSMISSIONS(JE_BATCH_ID)
When you post the invoice to GL This table will effected.
6)Terms
a)AP_TERMS_TL(TERMS_ID)
Contains Term information.
b)AP_INTERFACE_REJECTIONS
which could not be processed by Payables Open Interface Import
c)AP_BANK_ACCOUNTS_ALL
information about bank accounts

Important Queries Discrepancy in Onhand Qty

ORDERS WITH THE ITEM:

SELECT DISTINCT B.ORDER_NUMBER,
A.ORG_ID,
A.ORDERED_ITEM,
A.FLOW_STATUS_CODE
FROM APPS.OE_ORDER_LINES_ALL A,
APPS.OE_ORDER_HEADERS_ALL B,
APPS.MTL_SYSTEM_ITEMS_B C
WHERE C.SEGMENT1 IN ('&ITEM')
AND A.FLOW_STATUS_CODE NOT IN('CLOSED','CANCELLED')
AND A.HEADER_ID = B.HEADER_ID
AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID

ORDER BY A.ORDERED_ITEM


SELECT (ON_HAND_QUANTITY - ORDERED_QUANTITY) DIFF, GAP.*
  FROM (SELECT NVL(STOCK.ORGANIZATION_CODE, DEMD.ORDER_ORGANIZATION) ORG_CODE,
               NVL(STOCK.OH_EBS_ITEM, DEMD.ORD_EBS_ITEM) EBS_ITEM,
               STOCK.ON_HAND_ITEM,
               STOCK.OH_EBS_ITEM,
               NVL(STOCK.PRIMARY_TRANSACTION_QUANTITY, 0) ON_HAND_QUANTITY,
               DEMD.COUNTRY_CODE,
               DEMD.ORDER_ORGANIZATION,
               DEMD.ITEM_NUMBER,
               DEMD.ORD_EBS_ITEM,
               NVL(DEMD.ORDERED_QUANTITY, 0) ORDERED_QUANTITY
          FROM (SELECT MP.ORGANIZATION_CODE,
                       MIRB.ATTRIBUTE1 ON_HAND_ITEM,
                       MSI.SEGMENT1 OH_EBS_ITEM,
                       SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_TRANSACTION_QUANTITY
                  FROM APPS.MTL_ONHAND_QUANTITIES_DETAIL MOQD,
                       APPS.MTL_PARAMETERS               MP,
                       APPS.MTL_SYSTEM_ITEMS             MSI,
                       APPS.MTL_ITEM_REVISIONS_B         MIRB
                 WHERE 1 = 1
                   AND MOQD.ORGANIZATION_ID = MP.ORGANIZATION_ID
                   AND MOQD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                   AND MOQD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                   AND MOQD.ORGANIZATION_ID = MIRB.ORGANIZATION_ID
                   AND MOQD.INVENTORY_ITEM_ID = MIRB.INVENTORY_ITEM_ID
                   AND MOQD.REVISION = MIRB.REVISION
                       AND ORGANIZATION_CODE IN (
                       'C60', 'C65', 'F43'
                       )
                   AND SUBINVENTORY_CODE IN ('PICKLINE', 'STAGING')
                 GROUP BY MP.ORGANIZATION_CODE, MSI.SEGMENT1, MIRB.ATTRIBUTE1
                 ORDER BY ORGANIZATION_CODE, ON_HAND_ITEM) STOCK
          FULL JOIN (SELECT FLV.LOOKUP_CODE COUNTRY_CODE,
                           MP.ORGANIZATION_CODE ORDER_ORGANIZATION,
                           MSI.SEGMENT1 ORD_EBS_ITEM,
                           OOLA.USER_ITEM_DESCRIPTION ITEM_NUMBER,
                           SUM(OOLA.ORDERED_QUANTITY) ORDERED_QUANTITY
                      FROM APPS.OE_ORDER_HEADERS_ALL OOHA,
                           APPS.OE_ORDER_LINES_ALL   OOLA,
                           APPS.FND_LOOKUP_VALUES    FLV,
                           APPS.MTL_PARAMETERS       MP,
                           APPS.MTL_SYSTEM_ITEMS     MSI
                     WHERE 1 = 1
                       AND OOHA.HEADER_ID = OOLA.HEADER_ID
                       AND OOLA.SHIP_FROM_ORG_ID = MP.ORGANIZATION_ID
                       AND OOLA.CANCELLED_FLAG = 'N'
                       AND OOLA.SHIPPABLE_FLAG = 'Y'
                       AND OOLA.OPEN_FLAG = 'Y'
                       AND FLV.LANGUAGE = FLV.SOURCE_LANG
                       AND OOLA.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID
                       AND OOLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                       AND OOLA.LINE_CATEGORY_CODE = 'ORDER'
                       AND FLV.LOOKUP_TYPE IN ('XX_OM_COUNTRY_OU_MAPPING')
                       AND FLV.ATTRIBUTE1 = OOHA.ORG_ID
                       AND FLV.LOOKUP_CODE = SUBSTR(OOHA.ORIG_SYS_DOCUMENT_REF, 1, 3)
                       AND    OOHA.ATTRIBUTE2 IS NOT NULL
--                       AND TRUNC(TO_DATE(OOHA.ATTRIBUTE2)) < TRUNC(SYSDATE + 1)
                       AND EXISTS (SELECT 1 FROM APPS.XX_OM_ORDER_DELIVERIES_STG RP WHERE RP.CONSOLIDATED_ORDER_NUMBER =  OOHA.ATTRIBUTE7
                       AND RP.COUNTRY_CODE = FLV.LOOKUP_CODE AND RP.SHIP_DATE < TRUNC(SYSDATE) )
                       AND ORGANIZATION_CODE IN (
                       'C60', 'C65', 'F43'
                       )
                     GROUP BY FLV.LOOKUP_CODE,
                              MP.ORGANIZATION_CODE,
                              MSI.SEGMENT1,
                              OOLA.USER_ITEM_DESCRIPTION
                     ORDER BY ORGANIZATION_CODE, ITEM_NUMBER) DEMD
            ON DEMD.ITEM_NUMBER = STOCK.ON_HAND_ITEM
           AND STOCK.OH_EBS_ITEM = DEMD.ORD_EBS_ITEM
           AND DEMD.ORDER_ORGANIZATION = STOCK.ORGANIZATION_CODE) GAP
WHERE 1 = 1
  AND (ON_HAND_QUANTITY = 0 OR ORDERED_QUANTITY = 0 OR ON_HAND_QUANTITY <> ORDERED_QUANTITY)

;