Oracle Contracts and iStore Useful SQLS

-- Find MyAgreements Users Created

select fu.user_name
from applsys.fnd_user FU
where fu.user_name like '%_EXT'
AND FU.CUSTOMER_ID IS NOT NULL

-- Find MyAgreements Users who have logged in and Used MyAgreements


select FU.USER_NAME ,
COUNT(ICS.SESSION_ID)
from icx.icx_sessions ics,
applsys.fnd_user fu
where fu.user_name like '%_EXT'
AND FU.CUSTOMER_ID IS NOT NULL
AND FU.USER_ID = ICS.USER_ID
GROUP BY FU.USER_NAME
ORDER BY 2 DESC

-- Find Oracle Contract Agreements accepted through MyAgreements

SELECT DISTINCT
oebh.order_number,
fu4.user_name CREATED_BY,
FU.USER_NAME USER_ID,
SUBSTR(fu2.user_name, 1,INSTR(fu2.user_name,'_EXT')-1),
fu3.user_name,
oebh.last_update_date,
oebh.customer_signature_date
FROM applsys.fnd_lobs lob,
applsys.fnd_documents_tl docs,
applsys.fnd_document_categories_tl doccat,
applsys.fnd_attached_documents adocs,
ont.oe_blanket_headers_all oebh,
applsys.fnd_user fu,
applsys.fnd_user fu2,
applsys.fnd_user fu3,
applsys.fnd_user fu4
WHERE oebh.header_id = TO_NUMBER (adocs.pk1_value)
AND adocs.category_id = doccat.category_id
AND adocs.entity_name = 'OE_ORDER_HEADERS'
AND adocs.document_id = docs.document_id
AND docs.media_id = lob.file_id
and docs.created_by = fu.user_id
and fu.USER_ID = 4400
and UPPER(oebh.cust_po_number) = 'MANUAL'
and oebh.SOLD_TO_ORG_ID <> 715182
and to_number(oebh.attribute19) = fu2.customer_id
and oebh.LAST_UPDATED_BY = fu3.user_id
and oebh.created_by = fu4.user_id


-- Find Oracle Contract Agreements rejected today


select OEBH.order_number,
SUBSTR(FU.user_name, 1,INSTR(FU.user_name,'_EXT')-1) SIGNATORY,
SUBSTR(FU2.user_name, 1,INSTR(FU2.user_name,'_EXT')-1) UPDATED_BY
from ont.oe_blanket_headers_all oebh,
applsys.fnd_user fu,
applsys.fnd_user fu2
where oebh.flow_status_code = 'DRAFT_CUSTOMER_REJECTED'
AND TO_NUMBER(OEBH.ATTRIBUTE19) = FU.CUSTOMER_ID
AND OEBH.LAST_UPDATED_BY = FU2.USER_ID
AND FU.USER_ID = FU2.USER_ID
AND TRUNC(OEBH.LAST_UPDATE_DATE ) = TRUNC (SYSDATE)


-- Find the Template Names for a particular jsp file name

SELECT
b.item_id,
b.access_name,
t.item_name,
t.description,
b.applicable_to_code,
key.keyword,
map.lgl_phys_map_id,
map.msite_id,
map.default_site,
map.language_code,
map.default_language,
att.file_name
FROM
jtf.jtf_amv_items_b b,
jtf.jtf_amv_items_tl t,
ibe.ibe_dsp_lgl_phys_map map,
jtf.jtf_amv_attachments att,
jtf.jtf_amv_item_keywords key
WHERE
att.file_name like '%xxul%Landing%.jsp%' and
b.item_id = t.item_id AND
t.language = userenv('LANG') AND
b.application_id = 671 AND
b.deliverable_type_code = 'TEMPLATE' AND
b.item_id = map.item_id (+) AND
map.attachment_id = att.attachment_id (+) AND
b.item_id = key.item_id (+)