ASCPAlbert: 9月 2015

2015年9月3日星期四

APPS.JE_NO_GEI_SREPORT_V 會計科目餘額表

CREATE OR REPLACE FORCE VIEW APPS.JE_NO_GEI_SREPORT_V
(
   AUTHORITY_TYPE,
   PERIOD,
   ACTUAL_FLAG,
   ACCOUNT_NUMBER,
   BALANCE,
   TEMPLATE_ID,
   PERIOD_NUM,
   PERIOD_YEAR
)
AS
   SELECT gcc.segment4     authority_type,
          gbs.period_name period,
          gbs.actual_flag actual_flag,
          gcc.segment2     account_number,
          (gbs.period_net_dr - gbs.period_net_cr) balance,
          gbs.template_id template_id,
          gbs.period_num   period_num,
          gbs.period_year period_year
     FROM gl_code_combinations gcc,
          gl_balances gbs,
          gl_sets_of_books gso
    WHERE     gbs.code_combination_id = gcc.code_combination_id
          AND gbs.ledger_id           = gso.set_of_books_id
          AND gso.currency_code       = gbs.currency_code;
----簡直就是亂寫----gl_balances 是主題
gl_code_combinations 是抓出 segment4 AS authority_type
gl_sets_of_books     是限制
gl_balances.ledger_id    =gl_sets_of_books.set_of_books_id
gl_balances.currency_code=gl_sets_of_books.currency_code

2015年9月2日星期三

建立 WIP_OPERATIONS_V 重構連結方式

建立 WIP_OPERATIONS_V 重構連結方式

         FROM WIP_OPERATIONS          WO
     INNER JOIN BOM_DEPARTMENTS         BD ON WO.DEPARTMENT_ID         = BD.DEPARTMENT_ID
LEFT OUTER JOIN BOM_STANDARD_OPERATIONS BSO ON WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID
                                           AND BSO.LINE_ID IS NULL
                                           AND NVL (BSO.OPERATION_TYPE, 1) = 1
LEFT OUTER JOIN MFG_LOOKUPS             LU1 ON WO.SHUTDOWN_TYPE            = LU1.LOOKUP_CODE
                                           AND 'BOM_EAM_SHUTDOWN_TYPE'     = LU1.LOOKUP_TYPE
LEFT OUTER JOIN PER_ALL_PEOPLE_F        PAP ON WO.EMPLOYEE_ID            = PAP.PERSON_ID

以下寫法跟胡說八道幾乎相同
還是Oracle 原廠的瘋子寫的方式
原廠就是自大與偷懶...
不好意思說你智商低...

FROM BOM_DEPARTMENTS         BD,
            BOM_STANDARD_OPERATIONS BSO,
            WIP_OPERATIONS          WO,
            MFG_LOOKUPS             LU1,
            PER_ALL_PEOPLE_F        PAP
      WHERE     BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
            AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
            AND NVL (BSO.OPERATION_TYPE, 1) = 1
            AND BSO.LINE_ID IS NULL
            AND LU1.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
            AND LU1.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
            AND WO.EMPLOYEE_ID = PAP.PERSON_ID(+)

CEATE OR REPLACE FORCE VIEW APPS.WIP_OPERATIONS_V
( ROW_ID,
   WIP_ENTITY_ID,
   OPERATION_SEQ_NUM,
   ORGANIZATION_ID,
   REPETITIVE_SCHEDULE_ID,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN,
   REQUEST_ID,
   PROGRAM_APPLICATION_ID,
   PROGRAM_ID,
   PROGRAM_UPDATE_DATE,
   OPERATION_SEQUENCE_ID,
   STANDARD_OPERATION_ID,
   OPERATION_CODE,
   DEPARTMENT_ID,
   DEPARTMENT_CODE,
   LOCATION_ID,
   DESCRIPTION,
   SCHEDULED_QUANTITY,
   QUANTITY_IN_QUEUE,
   QUANTITY_RUNNING,
   QUANTITY_WAITING_TO_MOVE,
   QUANTITY_REJECTED,
   QUANTITY_SCRAPPED,
   QUANTITY_COMPLETED,
   FIRST_UNIT_START_DATE,
   FIRST_UNIT_COMPLETION_DATE,
   LAST_UNIT_START_DATE,
   LAST_UNIT_COMPLETION_DATE,
   PREVIOUS_OPERATION_SEQ_NUM,
   NEXT_OPERATION_SEQ_NUM,
   COUNT_POINT_TYPE,
   COUNT_POINT_FLAG,
   AUTOCHARGE_FLAG,
   BACKFLUSH_FLAG,
   MINIMUM_TRANSFER_QUANTITY,
   DATE_LAST_MOVED,
   ATTRIBUTE_CATEGORY,
   ATTRIBUTE1,
   ATTRIBUTE2,
   ATTRIBUTE3,
   ATTRIBUTE4,
   ATTRIBUTE5,
   ATTRIBUTE6,
   ATTRIBUTE7,
   ATTRIBUTE8,
   ATTRIBUTE9,
   ATTRIBUTE10,
   ATTRIBUTE11,
   ATTRIBUTE12,
   ATTRIBUTE13,
   ATTRIBUTE14,
   ATTRIBUTE15,
   OPERATION_YIELD,
   CUMULATIVE_SCRAP_QUANTITY,
   OPERATION_YIELD_ENABLED,
   OPERATION_COMPLETED,
   SHUTDOWN_TYPE,
   SHUTDOWN_TYPE_DISP,
   X_POS,
   Y_POS,
   LONG_DESCRIPTION,
   DISABLE_DATE,
   RECOMMENDED,
   PROGRESS_PERCENTAGE,
   WSM_BONUS_QUANTITY,
   ACTUAL_START_DATE,
   ACTUAL_COMPLETION_DATE,
   EMPLOYEE_ID,
   EMPLOYEE_NAME,
   LOWEST_ACCEPTABLE_YIELD,
   CHECK_SKILL
)
AS
     SELECT WO.ROWID ROW_ID,
            WO.WIP_ENTITY_ID,
            WO.OPERATION_SEQ_NUM,
            WO.ORGANIZATION_ID,
            WO.REPETITIVE_SCHEDULE_ID,
            WO.LAST_UPDATE_DATE,
            WO.LAST_UPDATED_BY,
            WO.CREATION_DATE,
            WO.CREATED_BY,
            WO.LAST_UPDATE_LOGIN,
            WO.REQUEST_ID,
            WO.PROGRAM_APPLICATION_ID,
            WO.PROGRAM_ID,
            WO.PROGRAM_UPDATE_DATE,
            WO.OPERATION_SEQUENCE_ID,
            WO.STANDARD_OPERATION_ID,
            BSO.OPERATION_CODE,
            WO.DEPARTMENT_ID,
            BD.DEPARTMENT_CODE,
            BD.LOCATION_ID,
            WO.DESCRIPTION,
            WO.SCHEDULED_QUANTITY,
            DECODE (WO.QUANTITY_IN_QUEUE, 0, NULL, WO.QUANTITY_IN_QUEUE),
            DECODE (WO.QUANTITY_RUNNING, 0, NULL, WO.QUANTITY_RUNNING),
            DECODE (WO.QUANTITY_WAITING_TO_MOVE,
                    0, NULL,
                    WO.QUANTITY_WAITING_TO_MOVE),
            DECODE (WO.QUANTITY_REJECTED, 0, NULL, WO.QUANTITY_REJECTED),
            DECODE (WO.QUANTITY_SCRAPPED, 0, NULL, WO.QUANTITY_SCRAPPED),
            DECODE (WO.QUANTITY_COMPLETED, 0, NULL, WO.QUANTITY_COMPLETED),
            WO.FIRST_UNIT_START_DATE,
            WO.FIRST_UNIT_COMPLETION_DATE,
            WO.LAST_UNIT_START_DATE,
            WO.LAST_UNIT_COMPLETION_DATE,
            WO.PREVIOUS_OPERATION_SEQ_NUM,
            WO.NEXT_OPERATION_SEQ_NUM,
            WO.COUNT_POINT_TYPE,
            DECODE (WO.COUNT_POINT_TYPE, 1, 1, 2) "COUNT_POINT_FLAG",
            DECODE (WO.COUNT_POINT_TYPE, 3, 2, 1) "AUTOCHARGE_FLAG",
            WO.BACKFLUSH_FLAG,
            WO.MINIMUM_TRANSFER_QUANTITY,
            WO.DATE_LAST_MOVED,
            WO.ATTRIBUTE_CATEGORY,
            WO.ATTRIBUTE1,
            WO.ATTRIBUTE2,
            WO.ATTRIBUTE3,
            WO.ATTRIBUTE4,
            WO.ATTRIBUTE5,
            WO.ATTRIBUTE6,
            WO.ATTRIBUTE7,
            WO.ATTRIBUTE8,
            WO.ATTRIBUTE9,
            WO.ATTRIBUTE10,
            WO.ATTRIBUTE11,
            WO.ATTRIBUTE12,
            WO.ATTRIBUTE13,
            WO.ATTRIBUTE14,
            WO.ATTRIBUTE15,
            WO.OPERATION_YIELD,
            WO.CUMULATIVE_SCRAP_QUANTITY,
            WO.OPERATION_YIELD_ENABLED,
            NVL (WO.OPERATION_COMPLETED, 'N'),
            WO.SHUTDOWN_TYPE,
            LU1.MEANING,
            WO.X_POS,
            WO.Y_POS,
            WO.LONG_DESCRIPTION,
            WO.DISABLE_DATE,
            WO.RECOMMENDED,
            WO.PROGRESS_PERCENTAGE,
            WO.WSM_BONUS_QUANTITY,
            WO.ACTUAL_START_DATE,
            WO.ACTUAL_COMPLETION_DATE,
            WO.EMPLOYEE_ID,
            PAP.FULL_NAME,
            WO.LOWEST_ACCEPTABLE_YIELD,
            NVL (wo.CHECK_SKILL, 2) CHECK_SKILL

           FROM WIP_OPERATIONS          WO
     INNER JOIN BOM_DEPARTMENTS         BD ON WO.DEPARTMENT_ID        = BD.DEPARTMENT_ID
LEFT OUTER JOIN BOM_STANDARD_OPERATIONS BSO ON WO.STANDARD_OPERATION_ID= BSO.STANDARD_OPERATION_ID AND BSO.LINE_ID IS NULL AND NVL (BSO.OPERATION_TYPE, 1) = 1
LEFT OUTER JOIN MFG_LOOKUPS             LU1 ON WO.SHUTDOWN_TYPE        = LU1.LOOKUP_CODE AND 'BOM_EAM_SHUTDOWN_TYPE'= LU1.LOOKUP_TYPE
LEFT OUTER JOIN PER_ALL_PEOPLE_F        PAP ON WO.EMPLOYEE_ID          = PAP.PERSON_ID

      WHERE     BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
            AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
            AND NVL (BSO.OPERATION_TYPE, 1) = 1
            AND BSO.LINE_ID IS NULL
            AND LU1.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
            AND LU1.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
            AND WO.EMPLOYEE_ID = PAP.PERSON_ID(+)
   ORDER BY WO.OPERATION_SEQ_NUM;

2015年9月1日星期二

ORACLE EBS CATEGORY 的資料表與主鍵 CATEGORY _ID

OWNER	TABLE_NAME	COLUMN_NAME		DATA_TYPE	DATA_LENGTH
AK	AK_CATEGORY_USAGES	CATEGORY_ID	NUMBER	22
APPS	AK_CATEGORY_USAGES_V	CATEGORY_ID	NUMBER	22
ASL	ASL_CATEGORY_SUMMARY_INFO	CATEGORY_ID	NUMBER	22
ASL	ASL_CATEGORY_SUMMARY_INFO	CATEGORY_SET_ID	NUMBER	22
ASL	ASL_INV_CATEGORY_ACC	CATEGORY_ID	NUMBER	22
ASL	ASL_INV_CATEGORY_ACC	CATEGORY_SET_ID	NUMBER	22
APPS	AS_INTEREST_CATEGORY_SETS	CATEGORY_SET_ID	NUMBER	22
OSM	AS_INTEREST_CATEGORY_SETS_ALL	CATEGORY_SET_ID	NUMBER	22
APPS	AS_INTEREST_CATEGORY_SETS_V	CATEGORY_SET_ID	NUMBER	22
BOM	BOM_ITEM_ATTACH_CATEGORY_ASSOC	ATTACH_CATEGORY_ID	NUMBER	22
APPS	CLN_INV_CATEGORY_V	CATEGORY_ID	NUMBER	22
APPS	CLN_ITEMMST_ITEMCATEGORY_V	CATEGORY_ID	NUMBER	22
APPS	CLN_ITEMMST_ITEMCATEGORY_V	CATEGORY_SET_ID	NUMBER	22
APPS	CLN_PROCAT_ITEMCATEGORY_V	CATEGORY_ID	NUMBER	22
APPS	CSC_PROF_CATEGORY_LOOKUPS_V	GROUP_CATEGORY_ID	NUMBER	22
APPS	CST_XLA_INV_CATEGORY_REF_V	CATEGORY_ID	NUMBER	22
APPS	CST_XLA_INV_CATEGORY_REF_V	CATEGORY_ITEM_ID	NUMBER	22
APPS	CST_XLA_INV_CATEGORY_REF_V	CATEGORY_ORGANIZATION_ID	NUMBER	22
APPS	CST_XLA_PLA_CATEGORY_REF_V	PLA_CATEGORY_ID	NUMBER	22
CS	CS_KB_CATEGORY_GROUPS_B	CATEGORY_GROUP_ID	NUMBER	22
CS	CS_KB_CATEGORY_GROUPS_TL	CATEGORY_GROUP_ID	NUMBER	22
APPS	CS_KB_CATEGORY_GROUPS_VL	CATEGORY_GROUP_ID	NUMBER	22
APPS	CUS_ITEM_CATEGORY	CATEGORY_ID	NUMBER	22
APPS	CUS_ITEM_CATEGORY_UPD3	CATEGORY_ID	NUMBER	22
APPS	CUS_MTL_CATEGORY_SETS_150604	CATEGORY_SET_ID	NUMBER	22
APPS	CUS_MTL_CATEGORY_SETS_150604	DEFAULT_CATEGORY_ID	NUMBER	22
APPS	CUS_SALE_CATEGORY_UPD1	CATEGORY_ID	NUMBER	22
DOM	DOM_CATEGORY_LIFECYCLES	CATEGORY_ID	NUMBER	22
DOM	DOM_CATEGORY_PUB_TEMPL	CATEGORY_ID	NUMBER	22
APPS	EDW_ITEMS_CATEGORY_FKV	CATEGORY_ID	NUMBER	22
APPS	ENI_ITEM_CATEGORY_V	PRODUCT_CATEGORY_ID	VARCHAR2	40
FA	FA_CATEGORY_BOOKS	CATEGORY_ID	NUMBER	22
FA	FA_CATEGORY_BOOK_DEFAULTS	CATEGORY_ID	NUMBER	22
APPLSYS	FND_DOC_CATEGORY_USAGES	CATEGORY_ID	NUMBER	22
APPLSYS	FND_DOC_CATEGORY_USAGES	DOC_CATEGORY_USAGE_ID	NUMBER	22
APPS	FND_DOC_CATEGORY_USAGES_VL	CATEGORY_ID	NUMBER	22
APPS	FND_DOC_CATEGORY_USAGES_VL	DOC_CATEGORY_USAGE_ID	NUMBER	22
APPLSYS	FND_SOA_LOG_CATEGORY	CATEGORY_ID	NUMBER	22
GMD	GMD_LCF_CATEGORY_DTL_GTMP	CATEGORY_ID	NUMBER	22
GMD	GMD_LCF_CATEGORY_HDR_GTMP	CATEGORY_ID	NUMBER	22
GMD	GMD_LCF_CATEGORY_HDR_GTMP	CATEGORY_SET_ID	NUMBER	22
GMI	GMI_CATEGORY_SETS	CATEGORY_SET_ID	NUMBER	22
APPS	GMP_PDR_CATEGORY_V	CATEGORY_SET_ID	NUMBER	22
APPS	HRBV_PROPOSAL_CATEGORY_MEMBE_V	PARENT_CATEGORY_TYPE_ID	NUMBER	22
APPS	HRBV_PROPOSAL_CATEGORY_MEMBE_V	PROPOSAL_CATEGORY_MEMBER_ID	NUMBER	22
APPS	HRBV_PROPOSAL_CATEGORY_MEMBE_V	PROPOSAL_CATEGORY_TYPE_ID	NUMBER	22
APPS	HRBV_PROPOSAL_CATEGORY_TYPES_V	PROPOSAL_CATEGORY_TYPE_ID	NUMBER	22
HXC	HXC_TIME_CATEGORY_COMPS	REF_TIME_CATEGORY_ID	NUMBER	22
HXC	HXC_TIME_CATEGORY_COMPS	TIME_CATEGORY_COMP_ID	NUMBER	22
HXC	HXC_TIME_CATEGORY_COMPS	TIME_CATEGORY_ID	NUMBER	22
APPS	HXC_TIME_CATEGORY_COMPS_V	REF_TIME_CATEGORY_ID	NUMBER	22
APPS	HXC_TIME_CATEGORY_COMPS_V	TIME_CATEGORY_COMP_ID	NUMBER	22
APPS	HXC_TIME_CATEGORY_COMPS_V	TIME_CATEGORY_ID	NUMBER	22
HXC	HXC_TIME_CATEGORY_COMP_SQL	TIME_CATEGORY_COMP_ID	NUMBER	22
HXC	HXC_TIME_CATEGORY_COMP_SQL	TIME_CATEGORY_COMP_SQL_ID	NUMBER	22
APPS	ICX_CATEGORY_LOV	CATEGORY_ID	NUMBER	22
APPS	ICX_CATEGORY_LOV	CATEGORY_SET_ID	NUMBER	22
APPS	ICX_CATEGORY_SET_LOV	CATEGORY_SET_ID	NUMBER	22
ICX	ICX_CAT_CATEGORY_ITEMS	RT_CATEGORY_ID	NUMBER	22
ICX	ICX_POR_CATEGORY_DATA_SOURCES	RT_CATEGORY_ID	NUMBER	22
ICX	ICX_POR_CATEGORY_DEFAULTS	RT_CATEGORY_ID	NUMBER	22
ICX	ICX_POR_CATEGORY_ITEMS	RT_CATEGORY_ID	NUMBER	22
ICX	ICX_POR_CATEGORY_ORDER_MAP	RT_CATEGORY_ID	NUMBER	22
IEM	IEM_EMAIL_CATEGORY_MAPS	KB_CATEGORY_ID	NUMBER	22
IGI	IGI_IAC_CATEGORY_BOOKS	CATEGORY_ID	NUMBER	22
IGI	IGI_MHC_CATEGORY_BOOKS	CATEGORY_ID	NUMBER	22
IGI	IGI_MHC_CATEGORY_BOOK_DEFAULTS	CATEGORY_ID	NUMBER	22
IGS	IGR_EPU_CATEGORY_UPG	PRODUCT_CATEGORY_ID	NUMBER	22
IGS	IGR_EPU_CATEGORY_UPG	PRODUCT_CATEGORY_SET_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_ASGNS	CATEGORY_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_ASGNS	CATEGORY_SET_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_TYPES	CATEGORY_SET_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_TYPES	DEFAULT_CATEGORY_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_TYPE_ASGNS	CATEGORY_ID	NUMBER	22
APPS	INVBV_ITEM_CATEGORY_TYPE_ASGNS	CATEGORY_SET_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_ASGNS	CATEGORY_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_ASGNS	CATEGORY_SET_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_TYPES	CATEGORY_SET_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_TYPES	DEFAULT_CATEGORY_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_TYPE_ASGNS	CATEGORY_ID	NUMBER	22
APPS	INVFV_ITEM_CATEGORY_TYPE_ASGNS	CATEGORY_SET_ID	NUMBER	22
APPS	LOS_API_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_API_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_ARR_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_ARR_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_ART_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_ART_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
XTR	LOS_CATEGORY_LINES	CATEGORY_ID	NUMBER	22
XTR	LOS_CATEGORY_LINES	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_GL_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_GL_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_NMB_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_NMB_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_NMN_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_NMN_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
APPS	LOS_XTR_CATEGORY_LINES_V	CATEGORY_ID	NUMBER	22
APPS	LOS_XTR_CATEGORY_LINES_V	CATEGORY_LINE_ID	NUMBER	22
MGDSYS	MGD_IDENCODING_CATEGORY	CATEGORY_ID	NUMBER	22
MGDSYS	MGD_IDENCODING_CATEGORY_TAB	CATEGORY_ID	NUMBER	22
MGDSYS	MGD_USR_IDENCODING_CATEGORY	CATEGORY_ID	NUMBER	22
INV	MLOG$_MTL_DEFAULT_CATEGORY	CATEGORY_SET_ID	NUMBER	22
APPS	MRP_AP_CATEGORY_SETS_V	CATEGORY_SET_ID	NUMBER	22
APPS	MRP_AP_DEFAULT_ITEM_CATEGORY_V	CATEGORY_ID	NUMBER	22
MSC	MSC_CATEGORY_SETS	CATEGORY_SET_ID	NUMBER	22
MSC	MSC_CATEGORY_SETS	SR_CATEGORY_SET_ID	NUMBER	22
MSC	MSC_CATEGORY_SET_ID_LID	CATEGORY_SET_ID	NUMBER	22
MSC	MSC_CATEGORY_SET_ID_LID	SR_CATEGORY_SET_ID	NUMBER	22
APPS	MSC_PUSH_CATEGORY_V	SR_CATEGORY_ID	NUMBER	22
MSC	MSC_RP_CATEGORY_MV	CATEGORY_SET_ID	NUMBER	22
MSC	MSC_RP_CATEGORY_MV	SR_CATEGORY_ID	NUMBER	22
MSC	MSC_ST_CATEGORY_SETS	CATEGORY_SET_ID	NUMBER	22
MSC	MSC_ST_CATEGORY_SETS	SOURCE_SR_CATEGORY_SET_ID	NUMBER	22
MSC	MSC_ST_CATEGORY_SETS	SR_CATEGORY_SET_ID	NUMBER	22
APPS	MSD_CATEGORY_SETS_V	CATEGORY_SET_ID	NUMBER	22
APPS	MSD_DEM_SR_CATEGORY_SETS_V	CATEGORY_SET_ID	NUMBER	22
INV	MTL_CATEGORY_ACCOUNTS	CATEGORY_ID	NUMBER	22
INV	MTL_CATEGORY_ACCOUNTS	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_ACCOUNTS_V	CATEGORY_ID	NUMBER	22
APPS	MTL_CATEGORY_ACCOUNTS_V	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_GRANTS_V	CATEGORY_ID	VARCHAR2	256
APPS	MTL_CATEGORY_GRANTS_V	CATEGORY_SET_ID	VARCHAR2	256
APPS	MTL_CATEGORY_SETS_ALL_V	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_SETS_ALL_V	DEFAULT_CATEGORY_ID	NUMBER	22
INV	MTL_CATEGORY_SETS_B	CATEGORY_SET_ID	NUMBER	22
INV	MTL_CATEGORY_SETS_B	DEFAULT_CATEGORY_ID	NUMBER	22
INV	MTL_CATEGORY_SETS_TL	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_SETS_V	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_SETS_V	DEFAULT_CATEGORY_ID	NUMBER	22
APPS	MTL_CATEGORY_SETS_VL	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_CATEGORY_SETS_VL	DEFAULT_CATEGORY_ID	NUMBER	22
INV	MTL_CATEGORY_SET_VALID_CATS	CATEGORY_ID	NUMBER	22
INV	MTL_CATEGORY_SET_VALID_CATS	CATEGORY_SET_ID	NUMBER	22
INV	MTL_CATEGORY_SET_VALID_CATS	PARENT_CATEGORY_ID	NUMBER	22
APPS	MTL_CATEGORY_SET_VALID_CATS_V	CATEGORY_ID	NUMBER	22
APPS	MTL_CATEGORY_SET_VALID_CATS_V	CATEGORY_SET_ID	NUMBER	22
INV	MTL_DEFAULT_CATEGORY_SETS	CATEGORY_SET_ID	NUMBER	22
APPS	MTL_DEFAULT_CATEGORY_SETS_FK_V	CATEGORY_SET_ID	NUMBER	22
MDSYS	OLS_DIR_CATEGORY_TYPES	CATEGORY_TYPE_ID	NUMBER	22
OTA	OTA_CATEGORY_USAGES	CATEGORY_USAGE_ID	NUMBER	22
OTA	OTA_CATEGORY_USAGES_TL	CATEGORY_USAGE_ID	NUMBER	22
APPS	OTA_CATEGORY_USAGES_V1	CATEGORY_USAGE_ID	NUMBER	22
APPS	OTA_CATEGORY_USAGES_VL	CATEGORY_USAGE_ID	NUMBER	22
HR	PAY_REPORT_CATEGORY_COMPONENTS	REPORT_CATEGORY_COMP_ID	NUMBER	22
HR	PAY_REPORT_CATEGORY_COMPONENTS	REPORT_CATEGORY_ID	NUMBER	22
APPS	PA_ITEM_CATEGORY_RES_V	ITEM_CATEGORY_ID	NUMBER	22
APPS	PA_REP_UTIL_CATEGORY_V	UTIL_CATEGORY_ID	NUMBER	22
HR	PER_PROPOSAL_CATEGORY_MEMBERS	PARENT_CATEGORY_TYPE_ID	NUMBER	22
HR	PER_PROPOSAL_CATEGORY_MEMBERS	PROPOSAL_CATEGORY_MEMBER_ID	NUMBER	22
HR	PER_PROPOSAL_CATEGORY_MEMBERS	PROPOSAL_CATEGORY_TYPE_ID	NUMBER	22
HR	PER_PROPOSAL_CATEGORY_TYPES	PROPOSAL_CATEGORY_TYPE_ID	NUMBER	22
APPS	PER_PROPOSAL_CATEGORY_TYPES_V	PROPOSAL_CATEGORY_TYPE_ID	NUMBER	22
APPS	POR_CATEGORY_LOV_V	CATEGORY_ID	NUMBER	22
APPS	POR_CATEGORY_LOV_V	CATEGORY_SET_ID	NUMBER	22
HR	PQH_TXN_CATEGORY_ATTRIBUTES	TRANSACTION_CATEGORY_ID	NUMBER	22
HR	PQH_TXN_CATEGORY_ATTRIBUTES	TXN_CATEGORY_ATTRIBUTE_ID	NUMBER	22
APPS	PQH_TXN_CATEGORY_ATTRIBUTES_V	TRANSACTION_CATEGORY_ID	NUMBER	22
APPS	PQH_TXN_CATEGORY_ATTRIBUTES_V	TXN_CATEGORY_ATTRIBUTE_ID	NUMBER	22
HR	PQH_TXN_CATEGORY_DOCUMENTS	TRANSACTION_CATEGORY_ID	NUMBER	22
RLM	RLM_MESSAGE_CATEGORY	MESSAGE_CATEGORY_ID	NUMBER	22
APPS	RLM_MESSAGE_CATEGORY_V	MESSAGE_CATEGORY_ID	NUMBER	22

ORACLE R12 客製化相關資料資料表串接欄位

參照來源
http://allfaqsforall.blogspot.tw/
但是一些沒驗證通過的已經依據 R12 驗證通過

GL AND AP
=========
GL_CODE_COMBINATIONS AP_INVOICES_ALL
code_combination_id = acct_pay_code_combination_id

GL_CODE_COMBINATIONS AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id = dist_code_combination_id

GL_SETS_OF_BOOKS AP_INVOICES_ALL
set_of_books_id = set_of_books_id

SELECT COUNT(*) --4919133
      FROM AP_INVOICE_DISTRIBUTIONS_ALL a
INNER JOIN GL_CODE_COMBINATIONS          b ON a.dist_code_combination_id=b.code_combination_id

SELECT COUNT(*) --874612
      FROM AP_INVOICES_ALL a
INNER JOIN GL_SETS_OF_BOOKS b ON a.set_of_books_id=b.set_of_books_id

SELECT COUNT(*) --4323869
      FROM RA_CUST_TRX_LINE_GL_DIST_ALL a
INNER JOIN GL_CODE_COMBINATIONS        b ON a.code_combination_id=b.code_combination_id

SELECT COUNT(*) --340594
      FROM MTL_SYSTEM_ITEMS_B   a
INNER JOIN GL_CODE_COMBINATIONS b ON a.cost_of_sales_account=b.code_combination_id

SELECT COUNT(*) --1755370
      FROM PO_DISTRIBUTIONS_ALL   a
INNER JOIN GL_CODE_COMBINATIONS   b ON a.code_combination_id=b.code_combination_id

GL AND AR
=========
GL_CODE_COMBINATIONS RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id = code_combination_id

GL AND INV
==========
GL_CODE_COMBINATIONS MTL_SYSTEM_ITEMS_B
code_combination_id = cost_of_sales_account

GL AND PO
=========
GL_CODE_COMBINATIONS PO_DISTRIBUTIONS_ALL
code_combination_id = code_combination_id

PO AND AP
==========
PO_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_ALL
po_distribution_id = po_distribution_id

PO_VENDORS AP_INVOICES_ALL
vendor_id = vendor_id

PO AND SHIPMENTS
=================
PO_HEADERS_ALL RCV_TRANSACTIONS
po_header_id = po_header_id

PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS
po_distribution_id = po_distribution_id

SHIPMENTS AND INVOICE
=====================
RCV_TRANSACTIONS AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID = RCV_TRANSACTION_ID

PO AND INV
==========
PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B
item_id = inventory_item_id
org_id = organization_id

PO AND HRMS
============
PO_HEADERS_ALL HR_EMPLOYEES
Agent_id = employee_id

PO AND REQUISITION
==================
PO_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id = distribution_id

SHIPMENTS AND INV
=================
RCV_TRANSACTIONS MTL_SYSTEM_ITEMS_B
Organization_id = organization_id

INV AND HRMS
============
MTL_SYSTEM_ITEMS_B HR_EMPLOYEES
buyer_id = employee_id

OM AND AR
==========
OE_ORDER_HEADERS_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number) = interface_line_attribute1
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id) = interface_line_attribute6

OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id = customer_trx_line_id

OM AND SHIPPING
===============
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS

HEADER_ID = SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
LINE_ID = SOURCE_LINE_ID

AP AND AR (BANKS)
=================
AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID

AP AND AR
=========
HZ_PARTIES AP_INVOICES_ALL
PARTY_ID = PARTY_ID

OM AND CRM
===========
OE_ORDER_LINES_ALL CSI_ITEM_INSTANCES(Install Base)
LINE_ID = LAST_OE_ORDER_LINE_ID

Table Name: Po_Requisition_Headers_All A
Column Names Table Name Column Name
A. REQUISITION_HEADER_ID PO_REQUISITION_LINES_ALL REQUISITION_HEADER_ID
A. TYPE_LOOKUP_CODE=PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
A. PREPARER_ID=PER_PEOPLE_F PERSON_ID
A. ORG_ID=MTL_SYSTEM_ITEMS ORGANIZATION_ID
A. ORG_ID=MTL_ORGANIZATIONS ORGANIZATION_ID

Table Name: Po_Requisition_Lines_All B
Column Names Table Name Column Name
B .REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID
B .REQUISITION_LINE_ID PO_REQ_DISTRIBUTIONS_ALL REQUISITION_LINE_ID
B .LINE_TYPE_ID PO_LINE_TYPES LINE_TYPE_ID
B .ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
B .ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID

Table Name: Po_Requisition_Distributions_All C .
Column Names Table Name Column Name
C .REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID
C .DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL REQ_DISTRIBUTION_ID
C .SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
C .CODE_COMBINATION_ID GL_CODE-COMBINATIONS CODE_COMBINATION_ID

Table Name: Po_Distributions_All D .
Column Names Table Name Column Name
D .PO_LINE_ID PO_LINES PO_LINE_ID
D .REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID
D .PO_DISTRIBUTION_ID AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID

Table Name: Po_Headers_All E .
Column Names Table Name Column Name
E .PO_HEADER_ID=>PO_LINES PO_HEADER_ID
E .PO_HEADER_ID=>RCV_SHIPMENT_LINES PO_HEADER_ID
E .VENDOR_ID PO_VENDORS VENDOR_ID
E .AGENT_ID PER_PEOPLE PERSON_ID
E .TYPE_LOOK_UP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE

Table Name: Po_Lines_All F.
Column Names Table Name Column Name
F.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
F.PO_LINE_ID PO_DISTRIBUTIONS_ALL PO_LINE_ID
F.ITEM_ID MTL_SYSTEM_ITEMS ITEM_ID

Table Name: Rcv_Shipment_Lines G.
Column Names Table Name Column Name
G.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
G.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS SHIPMENT_HEADER_ID

Table Name: Ap_Invoices_All H.
Column Names Table Name Column Name
H. INVOICE_ID => AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID

Table Name: Oe_Order_Headers_All I.
Column Names Table Name Column Name
I.HEADER_ID    => OE_ORDER_LINES HEADER_ID
I.SOURCE_HEADER_ID=> WISH_DELIVERY_DETAILS.SOURCE_HEADER_ID
I.PRICE_LIST_ID           => QP_LIST_HEADERS_TL.LIST_HEADER_ID
I.ORG_ID                      => MTL_ORGANIZATIONS.ORGANIZATION_ID
I.SALESREP_ID            => JTF_RS_SALESREPS.SALESREP_ID
I.ORDER_TYPE_ID       => OE_TRANSACTION_TYPES TRANSACTION_TYPE_ID
I.ORDER_SOURCE_ID => OE_ORDER_SOURCES ORDER_SOURCE_ID
I.ACCOUNTING_RULE_ID=> RA_RULES RULE_ID
I.PAYMENT_TERM_ID      => RA_TERMS TERM_ID
I.SOLD_TO_ORG_ID       => HZ_CUST_ACCOUNTS CUST_ACCOUNT_ID
I.SHIP_FROM_ORG_ID   => MTL_PARAMETERS ORGANIZATION_ID
I.SHIP_TO_ORG_ID        => HZ_CUST_SITE_USES_ALL SITE_USE_ID

Table Name: Oe_Order_Lines_All J.
Column Names Table Name Column Name
J.LINE_TYPE_ID OE_TRANSACTION_TYPES_TL TRANSACTION_TYPE_ID
J.INVOICING_RULE_ID RA_RULES RULE_ID

Table Name: Hz_Parties K.
Column Names Table Name Column Name
K.PATY_ID                   =>HZ_CUST_ACCOUNTS PATY_ID
K.CUST_ACCOUNT_ID=>OE_ORDER_LINES SOLD_TO_ORG_ID

Table Name: Hz_Party_Sites_All L.
Column Names Table Name Column Name
L.PATY_ID          =>HZ_PARTIES PATY_ID
L. LOCATION_ID=>HZ_LOCATIONS LOCATION_ID

Table Name: Wsh_delivery_details M.
Column Names Table Name Column Name
M.SOURCE_HEADER_ID=>OE_ORDER_HEADERS.SOURCE_HEADER_ID
M.DELIVERY_DETAIL_ID=>WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID
M.DELIVERY_ID             =>WSH_NEW_DELIVERIES DELIVERY_ID
M.INVENTORY_ITEM_ID=>MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID

Table Name: RA_CUSTOMER_TRX_ALL N.
Column Names Table Name Column Name
N.CUSTOMER_TRX_ID AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
N.TERM_ID RA_TERMS TERM_ID
N.CUSTOMER_TRX_ID RA_CUST_TRX_LINE_GL_DIST CUSTOMER_TRX_ID
Table Name: AR_CASH_REC EIPTS_ALL O.
Column Names Table Name Column Name
O.CASH_RECEIPT_ID AR_RECEIVABLE_APPLICATIONS_ALL CASH_RECEIPT_ID
O.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID

訂閱：意見 (Atom)

2015年9月3日 星期四