The query for GL Journal Created Approved and Posted Same User – 3 queries with the same Condition
GL Journal Created Approved and Posted Same User: Query -1
SELECT l.NAME “Ledger Name”,
SHORT_NAME “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id batch_id ,
b.Name “Batch Name”,
b.Description “Batch Desc”,
h.je_header_id header_id ,
h.Name “Journal Name” ,
h.description “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT User_name FROM fnd_user WHERE user_id=h.created_by) Entered_By,
(SELECT User_name FROM fnd_user WHERE user_id=b.posted_by) Posted_By,
appr.user_name approver_name,
h.creation_date,
b.POSTED_DATE
FROM gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(select item_key, ORIGINAL_RECIPIENT, MESSAGE_NAME, subject from wf_notifications where message_type = ‘GLBATCH’
and message_name in (‘NOTIFY_NO_APPROVAL_REQUIRED’, ‘REQUEST_APPROVAL’)) a_his
WHERE b.je_batch_id = h.je_batch_id
AND b.status =’P’
AND je_source = ‘Manual’
AND h.creation_date > to_date(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by =b.posted_by)
AND (b.created_by =appr.user_id)
AND ORIGINAL_RECIPIENT = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, instr(a_his.item_key, ‘*’)-1) = h.je_batch_id
ORDER BY h.je_header_id;
GL Journal Created Approved and Posted Same User: Query -2
SELECT l.NAME “Ledger Name”,
SHORT_NAME “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id batch_id ,
b.Name “Batch Name”,
b.Description “Batch Desc”,
h.je_header_id header_id ,
h.Name “Journal Name” ,
h.description “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT User_name FROM fnd_user WHERE user_id=h.created_by) Entered_By,
(SELECT User_name FROM fnd_user WHERE user_id=b.posted_by) Posted_By,
appr.user_name approver_name,
h.creation_date,
b.POSTED_DATE
FROM gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(select TEXT_VALUE, item_key
from wf_item_attribute_values
where item_type = ‘GLBATCH’
AND NAME IN (‘APPROVER_NAME’, ‘MANAGER_NAME’)) a_his
WHERE b.je_batch_id = h.je_batch_id
AND b.status =’P’
AND je_source = ‘Manual’
AND h.creation_date > to_date(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by =b.posted_by)
AND (b.created_by =appr.user_id)
AND TEXT_VALUE = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, instr(a_his.item_key, ‘*’)-1) = h.je_batch_id
ORDER BY h.je_header_id;
GL Journal Created Approved and Posted Same User: Query -3
SELECT l.NAME “Ledger Name”,
SHORT_NAME “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id batch_id ,
b.Name “Batch Name”,
b.Description “Batch Desc”,
h.je_header_id header_id ,
h.Name “Journal Name” ,
h.description “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT User_name FROM fnd_user WHERE user_id=h.created_by) Entered_By,
(SELECT User_name FROM fnd_user WHERE user_id=b.posted_by) Posted_By,
appr.user_name approver_name,
h.creation_date,
b.POSTED_DATE
FROM gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(SELECT s.ASSIGNED_USER,
item_key
FROM wf_item_activity_statuses S,
wf_process_activities P
WHERE 1 =1
AND ITEM_TYPE = ‘GLBATCH’
AND P.INSTANCE_ID = S.PROCESS_ACTIVITY
AND notification_id IS NOT NULL) a_his
WHERE b.je_batch_id = h.je_batch_id
AND b.status =’P’
AND je_source LIKE ‘Manual’
AND h.creation_date > to_date(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by =b.posted_by)
AND (b.created_by =appr.user_id)
AND ASSIGNED_USER = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, instr(a_his.item_key, ‘*’)-1) = h.je_batch_id
ORDER BY h.je_header_id;
Leave a Reply