2012年7月30日 星期一

GL - AP Table關連

AP Invoice:

image

AP Payment:

image

[GL]GL Interface 簡單範例

Interface table:
GL_INTERFACE

Base table:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program:
Journal Import

image

簡單範例:
1.
SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
  INTO V_GROUP_ID                 
  FROM DUAL;                      

SELECT CODE_COMBINATION_ID
  INTO V_CODE_COMBINATION_ID
  FROM GL_CODE_COMBINATIONS_V
WHERE CHART_OF_ACCOUNTS_ID = XXXX;
 
INSERT INTO GL_INTERFACE (
         STATUS,
         SET_OF_BOOKS_ID,
         ACCOUNTING_DATE,
         CURRENCY_CODE,
         DATE_CREATED,
         CREATED_BY,
         ACTUAL_FLAG,
         USER_JE_CATEGORY_NAME,
         USER_JE_SOURCE_NAME,
         BUDGET_VERSION_ID,
         CODE_COMBINATION_ID,
         PERIOD_NAME,
         ENTERED_DR,
         GROUP_ID)
VALUES ('NEW',                     
         :PARAMETER.SET_OF_BOOKS_ID,
         SYSDATE,          
         'TWD',                     
         SYSDATE,
         Fnd_Global.USER_ID,
         'B',                       
         'Accrual',
         'Spreadsheet',
         'test',
         V_CODE_COMBINATION_ID,
         V_PERIOD_NAME,
         100,
         V_GROUP_ID);

2.執行Journal Import

[INV]INV模組Base Table

Invenvtory (INV):
------------------------
1.MTL_SYSTEM_ITEMS_B           -- Master item info
2.MTL_ONHAND_QUANITTIES        -- Item onhand qty info
3.MTL_RESERVATIONS             -- Item Reservation info
4.MTL_MATERIAL_TRANSACTIONS    -- Item Transaction info
5.MTL_ITEM_LOCATIONS           -- Item location info
6,MTL_CATEGERIES               -- Item Category info
7.MTL_ITEM_CATEGORIES          -- Invemtry Categry
8.MTL_SECONDARY_INVENTORIES    -- Subinventories info
9.ORG_ORGANIZATION_DEFINITIONS -- Organizaition info
10.MTL_TRANSACTION_ACCOUNTS    -- Item Transaction info
11.MTL_TXN_SOURCE_TYPES        -- Item Transaction sources
12.MTL_PARAMETERS              -- Inventory Parameters

[PO]PO、AP模組Base Table

Purchase Order : (PO)
1.PO_REQUISITION_HEADERS_ALL -- Requisition Header info
2.PO_REQUISITION_LINES_ALL   -- Requisition Lines info
3.PO_REQ_DISTRIBUTIONS_ALL   -- Requisition Distribution info
4.PO_HEADERS_ALL             -- PO Header Info
5.PO_LINES_ALL               -- PO Line Info
6.PO_LINE_LOCATIONS_ALL      -- PO Line Shipment info
7.PO_DISTRIBUTIONS_ALL       -- PO Distribution info
8.RCV_SHIPMENT_HEADERS       -- Receiving header info
9.RCV_SHIPMENT_LINES         -- Receiving Lines info
10.RCV_TRANSACTIONS          -- Receiving transationd info
11.PO_VENDORS                -- Supplier Header info
12.PO_VENDOR_SITES_ALL       -- Supplier Site info
13.PO_VENDOR_SITE_CONTACTS   -- Supplier Site Contact info
14.HR_LOCATIONS              -- Supplier Site Address

Accounts Payable: (AP)
---------------------
1.AP_INVOICES_ALL               -- Invoice Header info
2.AP_INVOICE_DISTRIBUTIONS_ALL  -- Invoice Line info
3.AP_CHECKS_ALL                 -- Check info
4.AP_INVOICE_PAYMENTS_ALL       -- Invoice Payment info
5.AP_PAYMENT_SCHEDULES_ALL      -- Payment Schedule info
6.AP_HOLDS_ALL                  -- Invoice Holds info
7.AP_LOOKUP_CODES               -- Payable lookup info
8.PO_VENDORS                    -- Supplier Header info
9.PO_VENDOR_SITES_ALL           -- Supplier Site info
10.PO_VENDOR_SITE_CONTACTS      -- Supplier Site Contact info
11.HR_LOCATIONS                 -- Supplier Site Address
12.AP_BANKS                     -- Bank Info
13.AP_BANK_BRANCHES             -- Bank Branch info
14.AP_AE_HEADERS_ALL            -- Accounitng header info
15.AP_AE_LINES_ALL              -- Accounting Lines info
16.AP_AE_ACCOUNTING_EVETS       -- Accounting events info
17.AP_TERMS                     -- Payment Terms

[OM]OM、AR模組Base Table

Order Management (OM):

1.OE_ORDER_HEADERS_ALL    -- Order Header info
2.OE_ORDER_LINES_ALL      -- Order line info
3.OE_TRANSACTION_TYPES_TL -- Order type info
4.OE_ORDER_HOLDS          -- Order Hold info
4.OE_HOLDS_ALL              -- Order Hold info
5.OE_HOLD_SOURCES           -- Order Hold source info
6.OE_HOLD_RELEASES        -- Hold Release info
7.WSH_DELIVERY_DETAILS    -- Delivery Detial Info
8.WSH_NEW_DELIVERIES      -- Delivery Header info
9.WSH_DELIVERY_ASSIGNMENTS -- Delivery Assignments info
10.WSH_TRIP_STOPS          -- Delivery trips info
11.HZ_CUST_ACCOUNTS        -- Customer info
12.HZ_PARTIES              -- Party info
13.HZ_CUST_SITE_USES_ALL   -- Customer site use info
14.HZ_CUST_ACCT_SITES_ALL  -- Customer Site Acct info
15.HZ_PARTY_SITES          -- Party site info
16.HZ_LOCATIONS            -- Customer Site Adderess
17.WSH_LOOKUPS             -- Shipping lookup info

Accounts Receivables (AR):

1.RA_CUSTOMER_TRX_ALL            -- Receivable transaction info
2.RA_CUSTOMER_TRX_LINES_ALL      -- Transaction lines info
3.RA_CUST_TRX_LINE_GL_DIST_ALL   -- Transaction distribution info
4.AR_RECEIVABLE_APPLICATIONS_ALL -- Receiving application info
5.AR_CASH_RECEIPTS_ALL           -- Cash Receipt info
6.AR_TERMS                       -- Receivable Terms
7.HZ_CUST_ACCOUNTS               -- Customer info
8.HZ_PARTIES                     -- Party info
9.HZ_CUST_SITE_USES_ALL          -- Customer site use info
10.HZ_CUST_ACCT_SITES_ALL        -- Customer Site Acct info
11.HZ_PARTY_SITES                -- Party site info
12.HZ_LOCATIONS                  -- Customer Site Adderess

[INV] 透過MTL_SYSTEM_ITEMS_INTERFACE將Item欄位清成NULL

Updating Item Attributes to NULL
The method to update these columns to NULL is to use the following values:
for Numeric fields: insert -999999
for Character fields: insert '!'
for Date fields: the above list does not include any updateable date fields