Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, October 27, 2011

Get the Account Description:
SELECT
apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id, 1,
gcc.segment1) seg1,
gl_flexfields_pkg.get_description_sql(&char_of_account_id, 2, gcc.segment2)
seg2,
gl_flexfields_pkg.get_description_sql(&char_of_account_id, 3, gcc.segment3)
seg3,
gl_flexfields_pkg.get_description_sql(&char_of_account_id, 4, gcc.segment4)
seg4,
gl_flexfields_pkg.get_description_sql(&char_of_account_id, 5, gcc.segment5)
seg5
FROM
gl_code_combinations gcc
WHERE
gcc.segment4 = '25130' --gcc.code_combination_id = &code_combination_id;

Get inventory accounting entries linked TO GL:
SELECT
mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 account,
DECODE(SIGN(mta.transaction_value), 1, mta.transaction_value, 0, 0, NULL,
DECODE(SIGN(mta.base_transaction_value), 1, mta.base_transaction_value, NULL)
) Entered_Dr,
DECODE(SIGN(mta.transaction_value), -1, (-1 * mta.transaction_value), 0, 0,
NULL, DECODE(SIGN(mta.base_transaction_value), -1, (-1 *
mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value), 1, mta.base_transaction_value, 0, 0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value), -1, (-1 * mta.base_transaction_value
), 0, 0, NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
DECODE(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
FROM
inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
WHERE
mmt.organization_id = msi.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.transaction_id = mta.transaction_id
AND gcc.code_combination_id = mta.reference_account
AND mtt.transaction_type_id = mmt.transaction_type_id
AND gb.je_batch_id = gh.je_batch_id
AND gh.je_header_id = gl.je_header_id
AND gl.code_combination_id = mta.reference_account
AND mta.gl_batch_id = to_number(SUBSTR(gb.name, 1, instr(gb.name, ' ')
- 1))
AND gh.je_Category = 'MTL'
AND gh.je_source = 'Inventory'
AND gh.name = 'XXX' ---REPLACE XXX WITH NAME
AND gl.je_line_num = gr.je_line_num
AND gr.je_header_id = gl.je_header_id
AND gr.je_line_num = gl.je_line_num
AND mta.gl_batch_id = gr.reference_1
AND gh.period_name = '&period_name' -- ENTER THE PERIOD
AND UPPER(GB.NAME) LIKE UPPER('%&gl_batch_name%')
ORDER BY
1;


No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect