Home » Applications » Oracle Fusion Apps & E-Business Suite » adding xla tables to cst_ael_gl_inv_v
| adding xla tables to cst_ael_gl_inv_v [message #514012] |
Thu, 30 June 2011 14:22  |
 |
Arthi Siva
Messages: 7
Registered: June 2011
Location: US
|
Junior Member |
|
|
Regarding the standard view CST_AEL_GL_WIP_V:
Its the union of two selects.
Below mentioned are the from clause and wher clause of the selects:
SELECT -----
FROM gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
wip_transaction_accounts wta,
wip_transactions wt,
wip_entities we,
wip_lines wl,
cst_cost_elements cce,
wip_flow_schedules wfs,
po_headers poh,
bom_resources br,
mtl_transaction_reasons mtr,
bom_departments bd,
org_organization_definitions ood,
mtl_system_items_kfv msik,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mfg_lookups lu4,
mfg_lookups lu5
WHERE wta.transaction_id = wt.transaction_id
AND we.wip_entity_id = wt.wip_entity_id
AND wl.line_id(+) = wt.line_id
AND we.organization_id = wt.organization_id
AND bd.department_id(+) = wt.department_id
AND mtr.reason_id(+) = wt.reason_id
AND poh.po_header_id(+) = wt.po_header_id
AND cce.cost_element_id(+) = wta.cost_element_id
AND br.resource_id(+) = wta.resource_id
AND wfs.wip_entity_id(+) = we.wip_entity_id
AND lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE'
AND lu1.lookup_code(+) = wt.transaction_type
AND lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
AND lu2.lookup_code = wta.accounting_line_type
AND lu3.lookup_code(+) = wta.basis_type
AND lu3.lookup_type(+) = 'CST_BASIS'
AND lu4.lookup_code = 2
AND lu4.lookup_type = 'CST_VIEW_ACCOUNTING'
AND lu5.lookup_type = 'SYS_YES_NO'
AND lu5.lookup_code = DECODE (wta.gl_batch_id, -1, 2, 1)
AND sob.set_of_books_id = ood.set_of_books_id
AND gps.application_id = 401
AND gps.set_of_books_id = ood.set_of_books_id
AND gps.period_name = jeh.period_name /* AND wta.transaction_date BETWEEN gps.start_date AND (trunc(gps.end_date)+0.99999) */
AND wt.currency_conversion_type = glct.conversion_type(+)
AND wt.organization_id = ood.organization_id
AND jel.je_header_id = jeh.je_header_id
AND R.je_header_id = jeh.je_header_id
AND R.je_line_num = jel.je_line_num
AND sob.set_of_books_id = jel.ledger_id
AND R.gl_sl_link_id IS NOT NULL
AND R.reference_3 IS NOT NULL
AND wta.gl_batch_id = R.reference_1
AND wta.transaction_id = R.reference_3
AND wta.reference_account = jel.code_combination_id
AND wta.GL_SL_LINK_ID = R.gl_sl_link_id
AND jeh.je_source = 'Inventory'
AND jeh.je_category = 'WIP'
AND msik.inventory_item_id(+) = we.primary_item_id
AND msik.organization_id(+) = we.organization_id
UNION ALL
SELECT -----
FROM gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
wip_transaction_accounts wta,
wip_transactions wt,
wip_entities we,
wip_lines wl,
cst_cost_elements cce,
wip_flow_schedules wfs,
po_headers poh,
bom_resources br,
mtl_transaction_reasons mtr,
bom_departments bd,
org_organization_definitions ood,
mtl_system_items_kfv msik,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mfg_lookups lu4,
mfg_lookups lu5
WHERE wta.transaction_id = wt.transaction_id
AND we.wip_entity_id = wt.wip_entity_id
AND wl.line_id(+) = wt.line_id
AND we.organization_id = wt.organization_id
AND bd.department_id(+) = wt.department_id
AND mtr.reason_id(+) = wt.reason_id
AND poh.po_header_id(+) = wt.po_header_id
AND cce.cost_element_id(+) = wta.cost_element_id
AND br.resource_id(+) = wta.resource_id
AND wfs.wip_entity_id(+) = we.wip_entity_id
AND lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE'
AND lu1.lookup_code(+) = wt.transaction_type
AND lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
AND lu2.lookup_code = wta.accounting_line_type
AND lu3.lookup_code(+) = wta.basis_type
AND lu3.lookup_type(+) = 'CST_BASIS'
AND lu4.lookup_code = 2
AND lu4.lookup_type = 'CST_VIEW_ACCOUNTING'
AND lu5.lookup_type = 'SYS_YES_NO'
AND lu5.lookup_code = DECODE (wta.gl_batch_id, -1, 2, 1)
AND sob.set_of_books_id = ood.set_of_books_id
AND gps.application_id = 401
AND gps.set_of_books_id = ood.set_of_books_id
AND gps.period_name = jeh.period_name /* AND wta.transaction_date BETWEEN gps.start_date AND gps.end_date */
AND wt.currency_conversion_type = glct.conversion_type(+)
AND wt.organization_id = ood.organization_id
AND jel.je_header_id = jeh.je_header_id
AND R.je_header_id = jeh.je_header_id
AND R.je_line_num = jel.je_line_num
AND sob.set_of_books_id = jel.ledger_id
AND wta.gl_batch_id = R.reference_1
AND R.gl_sl_link_id IS NULL
AND R.reference_3 IS NULL
AND wta.reference_account + 0 = jel.code_combination_id
AND NVL (wta.currency_code, sob.currency_code) =
jeh.currency_code
AND jeh.je_source = 'Inventory'
AND jeh.je_category = 'WIP'
AND msik.inventory_item_id(+) = we.primary_item_id
AND msik.organization_id(+) = we.organization_id;
Wanted to introduce SLA tables in these. But noticed that we are no longer using CST_COST_ELEMENTS, dont see any data in this table for transactions done in R12 apart from upgraded data.
Used the same xla tables and links as mentioned in previous thread, changed (xld.source_distribution_type as 'WIP_TRANSACTION_ACCOUNTS' and xld.source_distribution_id_num_1=wip_sub_ledger_id from wip_transaction_accounts).
How to avoid using this CST_COST_ELEMENTS and fetch the required data.
Can you please guide me on this.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 07:48:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
