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)

;