Tuesday 31 January 2012

XLA_TRANSACTIONS




dgreybarrow SLA Technical
As discussed in earlier post, all subledger table is tighten with SLA specific table. Here is one of the example of AR.











SLA

responsibility_prompt

/* Formatted on 1/31/2012 2:54:26 PM (QP5 v5.115.810.9015) */
  SELECT   a.responsibility_id,
           a.responsibility_name,
           k.application_name,
           y.responsibility_key,
           d.user_menu_name menu_name,
           c.request_group_name request_group,
           k.application_name requset_application_name,
           x.data_group_name,
           k.application_name AS data_application_name,
           DECODE (b.rule_type,
                   'F',
                   'Function',
                   'M',
                   'Menu')
              "Rule Type",
           Z.PROMPT,
           e.user_function_name,
           f.user_menu_name
    FROM   fnd_responsibility_vl a,
           fnd_application_tl k,
           fnd_resp_functions b,
           fnd_request_groups c,
           fnd_menus_vl d,
           fnd_form_functions_tl e,
           FND_MENU_ENTRIES_VL z,                            ---  FND_MENUS_VL
           fnd_menus_tl f,
           fnd_data_groups x,
           fnd_responsibility y
   WHERE       a.data_group_id = x.data_group_id
           AND a.application_id = k.application_id
           AND y.responsibility_id = a.responsibility_id
           AND Z.MENU_ID(+) = D.MENU_ID
           AND a.application_id = b.application_id(+)
           AND a.responsibility_id = b.responsibility_id(+)
           AND a.application_id = c.application_id(+)
           AND a.request_group_id = c.request_group_id(+)
           AND a.menu_id = d.menu_id(+)
           AND b.action_id = e.function_id(+)
           AND b.action_id = f.menu_id(+)
           AND a.responsibility_name IN
                    ('GL Centra User SGU')    
GROUP BY   a.responsibility_id,
           a.responsibility_name,
           k.application_name,
           d.user_menu_name,
           k.application_name,
           c.request_group_name,
           k.application_name,
           x.data_group_name,
           k.application_name,
           DECODE (b.rule_type,
                   'F',
                   'Function',
                   'M',
                   'Menu'),
           Z.PROMPT,
           e.user_function_name,
           f.user_menu_name,
           y.responsibility_key
ORDER BY   a.responsibility_id

JOIN CONDITIONS USED IN ORACLE APPS(GL,AP,AR,INV ETC)

KEY JOINS


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

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 NamePo_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 NameRcv_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