In this post we will learn how to change session time out.
you can do it for any specific user by using user ID or you can ignore user ID if you want to change it for all user in oracle application
It is a system administrator job, So you must have system administrator responsibility.
Now, just follow below 3 images,
I have mentioned every single point in image by red circle, you just follow images.
At first you need to know your Java heap size,
If it is required to change or update Java heap size, Then please flow this post and you'll be able to change Java heap size as an Oracle AppsDBA.
SO, by this post you will know....
How to check Java heap size
How to change Java heap size
To check use below query....
SELECT service_id, service_handle, developer_parameters
FROM fnd_cp_services
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
Output will show like below screen...
Above output showing Java heap size 2048mb (2GB)
Now we will change or update it by another query
UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx3072m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
COMMIT;
Output will show like below screen...
Above output showing Java heap size 3072mb (3GB)
Please don't forget to commit.
I hope this post will help you to change or check Java heap size.
As an Oracle AppsDBA you will need this.
You can find me on Fiverr & LinkedIn
Here in this post we will show you a query for Draft Accounting.
You will use this query for inventory Module and it is a Inventory Accounting Query.
If you are a Oracle EBS report Developer And You are looking for a query of Oracle Inventory Draft Accounting, Then you are in right place.
Just Follow below query for Oracle Inventory Accounting / Draft Accounting.
If any part of this query you are unable to understand, then please feel free to comment below, I'll try my best to reply and help you.
This below query is tested and I'm using this query regularly for closing and reconciliation purpose. It's Working excellent for me and I'm updating this query often. I'll keep this post if I update any more.
Query for Oracle Inventory or Oracle Inventory Draft Accounting:
/* #############################
### Draft Accounting or you can say Inventory Accounting
### Query for Oracle EBS Inventory Module
##############################*/
SELECT INV_TRANS.ORGANIZATION_CODE ORG,
INV_TRANS.GRN,
INV_TRANS.TXN_ID,
INV_TRANS.XLA_HEADER_ID,
FIN_CAT.MEJOR_FIN_CAT,
INV_TRANS.ITEM_CODE,
INV_TRANS.EVENT_TYPE_CODE,
CASE
WHEN INV_TRANS.TRANSACTION_TYPE_ID = -99
THEN
INV_TRANS.EVENT_TYPE_CODE
ELSE
INV_TRANS.TRANSACTION_TYPE_NAME
END
TRX_TYPE_NAME,
0 OP_QTY,
0 OP_VAL,
SUM (
CASE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN
(17, 12, 44, 18, 61, 1002, 1003)
THEN
INV_TRANS.TRANSACTION_QUANTITY
ELSE
0
END)
RCV_QTY,
SUM (
CASE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (17, 18, 1002, 1003)
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (44)
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (61)
THEN
INV_TRANS.ACCOUNTED_DR
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (12)
THEN
INV_TRANS.ACCOUNTED_DR
WHEN NVL (INV_TRANS.TRANSACTION_TYPE_ID, 11111) = 11111
AND EVENT_TYPE_CODE = 'GLCOSTALOC'
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN NVL (INV_TRANS.TRANSACTION_TYPE_ID, 2222) = 2222
AND EVENT_TYPE_CODE = 'LC_ADJUST_VALUATION'
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID = -99
THEN
INV_TRANS.TRANSACTION_VALUE
ELSE
0
END)
RCV_VAL,
SUM (
CASE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN
(32,
35,
36,
42,
43,
15,
21,
63,
101,
103,
104,
106,
107,
111,
120,
10008,
140,
71,
200,
240,
241,
260,
261,
52)
THEN
INV_TRANS.TRANSACTION_QUANTITY
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (33)
AND NVL (INV_TRANS.ACCOUNTED_DR, 1) > 0
THEN
0
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (33)
AND NVL (INV_TRANS.ACCOUNTED_DR, 1) = 0
THEN
0
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (62)
AND NVL (INV_TRANS.ACCOUNTED_DR, 0) > 0
THEN
INV_TRANS.TRANSACTION_QUANTITY
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (62)
AND NVL (INV_TRANS.ACCOUNTED_CR, 0) > 0
THEN
INV_TRANS.TRANSACTION_QUANTITY
ELSE
0
END)
ISSUE_QTY,
SUM (
CASE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN
(32,
35,
36,
42,
43,
21,
63,
101,
103,
104,
106,
107,
111,
120,
10008,
140,
71,
200,
240,
241,
260,
261,
52)
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (15)
THEN
INV_TRANS.ACCOUNTED_DR
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (33)
AND NVL (INV_TRANS.ACCOUNTED_DR, 0) > 0
THEN
INV_TRANS.ACCOUNTED_DR
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (33)
AND NVL (INV_TRANS.ACCOUNTED_DR, 0) = 0
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (62)
AND NVL (INV_TRANS.ACCOUNTED_DR, 0) > 0
THEN
INV_TRANS.TRANSACTION_VALUE
WHEN INV_TRANS.TRANSACTION_TYPE_ID IN (62)
AND NVL (INV_TRANS.ACCOUNTED_CR, 0) > 0
THEN
INV_TRANS.TRANSACTION_VALUE
ELSE
0
END)
ISSUE_VAL,
SUM (NVL (INV_TRANS.TRANSACTION_QUANTITY, 0)) TRANS_QTY,
INV_TRANS.PRIMARY_UOM_CODE UOM,
SUM (NVL (INV_TRANS.TRANSACTION_VALUE, 0)) TRANS_VAL,
INV_TRANS.ACCOUNT_NAME,
INV_TRANS.SUB_ACCOUNT,
INV_TRANS.je_category_name,
INV_TRANS.accounting_class_code,
SUM (INV_TRANS.ACCOUNTED_DR) DR,
SUM (INV_TRANS.ACCOUNTED_CR) CR,
INV_TRANS.ACCOUNTING_CODE,
FIN_CAT.FIN_CAT,
INV_TRANS.ORGANIZATION_ID ORG_ID,
INV_TRANS.LEDGER_ID LE_ID,
INV_TRANS.OPERATING_UNIT OU,
INV_TRANS.INVENTORY_ITEM_ID ITEM_ID,
INV_TRANS.TRANSACTION_TYPE_ID TRX_TYPE_ID
FROM ( SELECT TO_CHAR (transaction_date, 'MON-YY') PERIOD_CODE,
(CASE
WHEN a.transaction_type_id IN (18, 36)
THEN
NVL (
(SELECT rsh.RECEIPT_NUM
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE rsh.shipment_header_id =
rsl.shipment_header_id
AND rsl.shipment_header_id =
rt.shipment_header_id
AND rsl.SHIPMENT_LINE_ID =
rt.SHIPMENT_LINE_ID
AND rt.transaction_id =
mmt.RCV_TRANSACTION_ID
AND mmt.transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 33
THEN
NVL ( (SELECT SHIPMENT_NUMBER
FROM mtl_material_transactions
WHERE transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id IN
(63, 103, 104, 107, 120, 200, 106)
THEN
NVL (
(SELECT MTRH.REQUEST_NUMBER
FROM MTL_TXN_REQUEST_HEADERS MTRH,
MTL_TXN_REQUEST_LINES MTRL,
MTL_MATERIAL_TRANSACTIONS MMT
WHERE MTRH.header_id = MTRL.header_id
AND MTRL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND MMT.transaction_id = a.transaction_id),
0)
WHEN a.transaction_type_id = 62
AND a.event_type_code =
'FOB_SHIP_SENDER_SHIP_NO_TP'
THEN
NVL ( (SELECT SHIPMENT_NUMBER
FROM mtl_material_transactions
WHERE transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 62
AND a.event_type_code =
'FOB_SHIP_RECIPIENT_SHIP_NO_TP'
THEN
NVL (
(SELECT rmv.RECEIPT_NUM
FROM rcv_msh_v rmv,
mtl_material_transactions mmt
WHERE rmv.SHIPMENT_NUM = mmt.SHIPMENT_NUMBER
AND mmt.transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 21
AND a.event_type_code =
'FOB_SHIP_SENDER_SHIP_NO_TP'
THEN
NVL ( (SELECT SHIPMENT_NUMBER
FROM mtl_material_transactions
WHERE transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 21
AND a.event_type_code =
'FOB_SHIP_RECIPIENT_SHIP_NO_TP'
THEN
NVL (
(SELECT rmv.RECEIPT_NUM
FROM rcv_msh_v rmv,
mtl_material_transactions mmt
WHERE rmv.SHIPMENT_NUM = mmt.SHIPMENT_NUMBER
AND mmt.transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 62
AND a.event_type_code =
'FOB_SHIP_RECIPIENT_SHIP_TP'
THEN
NVL (
(SELECT rmv.RECEIPT_NUM
FROM rcv_msh_v rmv,
mtl_material_transactions mmt
WHERE rmv.SHIPMENT_NUM = mmt.SHIPMENT_NUMBER
AND mmt.transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id IN (21, 62)
AND a.event_type_code = 'FOB_SHIP_SENDER_SHIP_TP'
THEN
NVL ( (SELECT SHIPMENT_NUMBER
FROM mtl_material_transactions
WHERE transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id IN (61, 12)
AND a.event_type_code = 'FOB_SHIP_RECIPIENT_RCPT'
THEN
NVL (
(SELECT rmv.RECEIPT_NUM
FROM rcv_msh_v rmv,
mtl_material_transactions mmt
WHERE rmv.SHIPMENT_NUM = mmt.SHIPMENT_NUMBER
AND mmt.transaction_Id = a.transaction_Id),
0)
WHEN a.transaction_type_id = 10008
AND a.event_type_code = 'COGS_RECOGNITION'
THEN
NVL ( (SELECT TRANSACTION_SOURCE_NAME
FROM mtl_material_transactions
WHERE transaction_Id = a.transaction_Id),
0)
WHEN a.event_type_code IN
('RECEIVE',
'DELIVER_EXPENSE',
'RET_TO_VENDOR',
'LC_ADJUST_DELIVER',
'LC_ADJUST_RECEIVE',
'LC_ADJUST_VALUATION')
THEN
NVL (
(SELECT rsh.RECEIPT_NUM
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsh.shipment_header_id =
rsl.shipment_header_id
AND rsl.shipment_header_id =
rt.shipment_header_id
AND rsl.SHIPMENT_LINE_ID =
rt.SHIPMENT_LINE_ID
AND a.SOURCE_LINE_ID = rt.transaction_Id
AND a.ENTITY_CODE = 'PURCHASING'),
0)
WHEN a.transaction_type_id IN
(44, 35, 1002, 1003, 17, 43)
THEN
NVL (
(SELECT gbh.batch_no
FROM gme_batch_header gbh,
mtl_material_transactions mmt
WHERE gbh.BATCH_ID = mmt.TRANSACTION_SOURCE_ID
AND gbh.organization_id =
mmt.organization_id
AND mmt.transaction_Id = a.transaction_Id),
0)
ELSE
NULL
END)
GRN,
C.ORGANIZATION_CODE,
A.ORGANIZATION_ID,
A.LEDGER_ID,
A.OPERATING_UNIT,
a.TRANSACTION_ID TXN_ID,
A.INVENTORY_ITEM_ID,
B.SEGMENT1
|| '|'
|| B.SEGMENT2
|| '|'
|| B.SEGMENT3
|| '|'
|| B.SEGMENT4
ITEM_CODE,
a.transaction_id,
a.event_class_code,
a.event_type_code,
g.transaction_type_id,
g.transaction_type_name,
d.je_category_name,
d.AE_HEADER_ID XLA_HEADER_ID,
e.accounting_class_code,
d.ae_header_id,
XX_AP_PKG.GET_ACCOUNT_DESC_FROM_CCID (
e.CODE_COMBINATION_ID)
ACCOUNT_NAME,
XX_AP_PKG.GET_SUB_ACCOUNT_DESC_FROM_CCID (
TO_NUMBER (e.CODE_COMBINATION_ID))
SUB_ACCOUNT,
TRANSACTION_QUANTITY,
PRIMARY_UOM_CODE,
TRANSACTION_VALUE,
SUM (NVL (e.accounted_dr, 0)) accounted_dr,
SUM (NVL (e.accounted_cr, 0)) accounted_cr,
f.concatenated_segments accounting_code
FROM GMF_XLA_EXTRACT_HEADERS a,
mtl_system_items_b b,
org_organization_definitions c,
xla_ae_headers d,
xla_ae_lines e,
gl_code_combinations_kfv f,
mtl_transaction_types g
WHERE 1 = 1
AND a.inventory_item_id = b.inventory_item_id(+)
AND a.organization_id = b.organization_id(+)
AND A.organization_id = c.organization_id
AND a.transaction_type_id = g.transaction_type_id(+)
AND a.event_id = d.event_id
AND d.ae_header_id = e.ae_header_id
AND e.code_combination_id = f.code_combination_id
AND (NVL (e.ACCOUNTED_DR, 0) > 0.49
OR NVL (e.ACCOUNTED_CR, 0) > 0.49)
AND TO_CHAR (transaction_date, 'MON-YY') = :P_PERIOD_CODE
AND a.ledger_id = :P_LEDGER_ID
GROUP BY transaction_date,
a.event_type_code,
C.ORGANIZATION_CODE,
A.ORGANIZATION_ID,
A.LEDGER_ID,
A.OPERATING_UNIT,
a.TRANSACTION_ID,
A.INVENTORY_ITEM_ID,
B.SEGMENT1
|| '|'
|| B.SEGMENT2
|| '|'
|| B.SEGMENT3
|| '|'
|| B.SEGMENT4,
a.transaction_id,
a.event_class_code,
a.event_type_code,
g.transaction_type_id,
g.transaction_type_name,
a.transaction_type_id,
d.je_category_name,
d.AE_HEADER_ID,
e.accounting_class_code,
d.ae_header_id,
XX_AP_PKG.GET_ACCOUNT_DESC_FROM_CCID (
e.CODE_COMBINATION_ID),
XX_AP_PKG.GET_SUB_ACCOUNT_DESC_FROM_CCID (
TO_NUMBER (e.CODE_COMBINATION_ID)),
TRANSACTION_QUANTITY,
PRIMARY_UOM_CODE,
TRANSACTION_VALUE,
f.concatenated_segments,
a.SOURCE_LINE_ID,
a.ENTITY_CODE) INV_TRANS
LEFT OUTER JOIN
(SELECT MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID,
MC.SEGMENT1 MEJOR_FIN_CAT,
MC.SEGMENT1 || '|' || MC.SEGMENT2 FIN_CAT
FROM MTL_ITEM_CATEGORIES MIC, MTL_CATEGORIES MC
WHERE MIC.CATEGORY_ID = MC.CATEGORY_ID AND STRUCTURE_ID = 50408) FIN_CAT
ON FIN_CAT.INVENTORY_ITEM_ID = INV_TRANS.INVENTORY_ITEM_ID
AND FIN_CAT.ORGANIZATION_ID = INV_TRANS.ORGANIZATION_ID
GROUP BY INV_TRANS.PRIMARY_UOM_CODE,
GRN,
INV_TRANS.TXN_ID,
INV_TRANS.XLA_HEADER_ID,
FIN_CAT.MEJOR_FIN_CAT,
FIN_CAT.FIN_CAT,
INV_TRANS.ORGANIZATION_CODE,
INV_TRANS.LEDGER_ID,
INV_TRANS.OPERATING_UNIT,
INV_TRANS.ORGANIZATION_ID,
INV_TRANS.INVENTORY_ITEM_ID,
INV_TRANS.ITEM_CODE,
INV_TRANS.EVENT_TYPE_CODE,
INV_TRANS.TRANSACTION_TYPE_NAME,
INV_TRANS.ACCOUNT_NAME,
INV_TRANS.SUB_ACCOUNT,
INV_TRANS.ACCOUNTING_CODE,
INV_TRANS.TRANSACTION_TYPE_ID,
INV_TRANS.je_category_name,
INV_TRANS.accounting_class_code
You can find me on Fiverr & LinkedIn
