Reports 2017-11-18T12:27:27+00:00

FND Attached Documents

Description
Categories: Application, Enginatics
FND attachment documents stored in the fnd_lobs table and their corresponding application entity e.g. sales orders that they are attached to.

select
x.*
from
(
select
fdev.application_name appliction,
nvl(fdev.table_name,fad.entity_name) table_name,
case
when fad.entity_name in ('PO_RELEASES','PO_REL') then (select haou.name||': '||pha.segment1||': '||pra.release_num from hr_all_organization_units haou, po_headers_all pha, po_releases_all pra where fad.pk1_value=pra.po_release_id and pra.org_id=haou.organization_id and pra.po_header_id=pha.po_header_id)
when fad.entity_name='MTL_SYSTEM_ITEMS' then (select mp.organization_code||': '||msib.segment1 from mtl_parameters mp, mtl_system_items_b msib where fad.pk1_value=msib.organization_id and fad.pk2_value=msib.inventory_item_id and msib.organization_id=mp.organization_id)
when fad.entity_name='OE_ORDER_HEADERS' then (select haou.name||': '||ooha.order_number from hr_all_organization_units haou, oe_order_headers_all ooha where fad.pk1_value=ooha.header_id and haou.organization_id=ooha.org_id)
when fad.entity_name='OE_ORDER_LINES' then (select haou.name||': '||ooha.order_number||': '||rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') from hr_all_organization_units haou, oe_order_headers_all ooha, oe_order_lines_all oola where fad.pk1_value=oola.line_id and ooha.header_id=oola.header_id and oola.org_id=haou.organization_id)
when fad.entity_name in ('PO_HEADERS','PO_HEAD') then (select haou.name||': '||pha.segment1 from hr_all_organization_units haou, po_headers_all pha where fad.pk1_value=pha.po_header_id and pha.org_id=haou.organization_id)
when fad.entity_name='BOM_OPERATION_SEQUENCES' then (
select
mp.organization_code||': '||msib.segment1||': '||bor.alternate_routing_designator||': '||bos.operation_seq_num
from
mtl_parameters mp,
bom_operational_routings bor,
mtl_system_items_b msib,
bom_operation_sequences bos
where
fad.pk1_value=bos.operation_sequence_id and
mp.organization_id=bor.organization_id and
bor.assembly_item_id=msib.inventory_item_id and
bor.organization_id=msib.organization_id and
bor.routing_sequence_id=bos.routing_sequence_id
)
when fad.entity_name='IBY_PAY_INSTRUCTIONS_ALL' then (select ipia.pay_admin_assigned_ref_code from iby_pay_instructions_all ipia where fad.pk1_value=ipia.payment_instruction_id)
when fad.entity_name='WIP_DISCRETE_OPERATIONS' then (select mp.organization_code from mtl_parameters mp where fad.pk3_value=mp.organization_id)||': '||(select we.wip_entity_name from wip_entities we where fad.pk1_value=we.wip_entity_id)||': '||fad.pk2_value
when fad.entity_name='WSH_DELIVERY_DETAILS' then (select haou.name||': '||wdd.source_header_number||': '||wdd.source_line_number from wsh_delivery_details wdd, hr_all_organization_units haou where fad.pk1_value=wdd.delivery_detail_id and wdd.org_id=haou.organization_id(+))
when fad.entity_name='PO_LINES' then (select haou.name||': '||pha.segment1||': '||pla.line_num from po_lines_all pla, po_headers_all pha, hr_all_organization_units haou where fad.pk1_value=pla.po_line_id and pla.po_header_id=pha.po_header_id and pla.org_id=haou.organization_id(+))
when fad.entity_name='PO_HEADERS' then (select haou.name||': '||pha.segment1 from po_headers_all pha, hr_all_organization_units haou where fad.pk1_value=pha.po_header_id and pha.org_id=haou.organization_id(+))
when fad.entity_name='RA_CUSTOMER_TRX' then (select haou.name||': '||rcta.trx_number from ra_customer_trx_all rcta, hr_all_organization_units haou where fad.pk1_value=rcta.customer_trx_id and rcta.org_id=haou.organization_id(+))
when fad.entity_name='QA_RESULTS' then (select mp.organization_code||': '||(select qp.name from qa_plans qp where fad.pk3_value=qp.plan_id)||': '||fad.pk2_value from qa_results qr, mtl_parameters mp where fad.pk3_value=qr.plan_id and fad.pk2_value=qr.collection_id and fad.pk1_value=qr.occurrence and qr.organization_id=mp.organization_id)
when fad.entity_name='WSH_NEW_DELIVERIES' then (select mp.organization_code||': '||wnd.name from wsh_new_deliveries wnd, mtl_parameters mp where fad.pk1_value=wnd.delivery_id and wnd.organization_id=mp.organization_id)
when fad.entity_name='AR_CUSTOMERS' then (select hca.account_number||': '||hca.account_name from hz_cust_accounts hca where fad.pk1_value=hca.cust_account_id)
when fad.entity_name='WIP_DISCRETE_JOBS' then (select mp.organization_code||': '||we.wip_entity_name from wip_entities we, wip_discrete_jobs wdj, mtl_parameters mp where fad.pk1_value=wdj.wip_entity_id and fad.pk2_value=wdj.organization_id and we.wip_entity_id=wdj.wip_entity_id and wdj.organization_id=mp.organization_id)
/*when fad.entity_name='PO_SHIPMENTS'
when fad.entity_name='RA_CUSTOMER_TRX_LINES'
when fad.entity_name='PO_RELEASES'
when fad.entity_name='REQ_LINES'
when fad.entity_name='AP_INVOICES'*/
else
trim('.' from fad.pk1_value||'.'||fad.pk2_value||'.'||fad.pk3_value||'.'||fad.pk4_value||'.'||fad.pk5_value)
end reference,
fad.seq_num,
fdcv.user_name category,
fdt.title,
fdt.description,
fdd.user_name data_type,
decode(fd.datatype_id,5,fd.url,nvl(fl.file_name,fd.file_name)) name,
decode(fd.datatype_id,1,to_clob(fdst.short_text),2,xxen_util.long_to_clob('FND_DOCUMENTS_LONG_TEXT','LONG_TEXT',fdlt.rowid)) text,
length(fl.file_data) file_size,
fl.file_content_type content_type,
lower(fl.file_format) file_format,
fl.expiration_date,
xxen_util.meaning(fd.usage_type,'ATCHMT_DOCUMENT_TYPE',0) usage,
decode(fd.security_type,1,'Organization',2,'Set of Books',3,'Business Unit',4,'None') security_type,
decode(fd.security_type,1,haou.name,2,gl.name) security_owner,
decode(fd.publish_flag,'Y','Y') share_,
fd.start_date_active,
fd.end_date_active,
fd.creation_date,
xxen_util.user_name(fd.created_by) created_by,
fl.program_name,
fd.request_id,
fcpv.user_concurrent_program_name concurrent_program,
fd.program_update_date
from
fnd_documents fd,
fnd_documents_tl fdt,
fnd_document_datatypes fdd,
fnd_document_categories_vl fdcv,
hr_all_organization_units haou,
gl_ledgers gl,
fnd_lobs fl,
fnd_concurrent_programs_vl fcpv,
fnd_documents_short_text fdst,
fnd_documents_long_text fdlt,
(select fad.* from fnd_attached_documents fad where '&show_attachment_objects'='Y') fad,
fnd_document_entities_vl fdev
where
1=1 and
fd.document_id=fdt.document_id and
fdt.language=userenv('lang') and
fd.datatype_id=fdd.datatype_id and
fdd.language=userenv('lang') and
fd.category_id=fdcv.category_id and
decode(fd.security_type,1,fd.security_id)=haou.organization_id(+) and
decode(fd.security_type,2,fd.security_id)=gl.ledger_id(+) and
fd.media_id=fl.file_id(+) and
fd.program_application_id=fcpv.application_id(+) and
fd.program_id=fcpv.concurrent_program_id(+) and
decode(fd.datatype_id,1,fd.media_id)=fdst.media_id(+) and
decode(fd.datatype_id,2,fd.media_id)=fdlt.media_id(+) and
fd.document_id=fad.document_id(+) and
fad.entity_name=fdev.data_object_code(+)
) x
where
2=2
order by
x.creation_date desc,
x.seq_num

Parameter Name SQL text Validation
Category
fdcv.user_name=:category
LOV
Datatype
fdd.user_name=:datatype
LOV
File Name contains
lower(fl.file_name) like '%'||:file_name||'%'
Short Text contains
fd.media_id in (select fdst.media_id from fnd_documents_short_text fdst where lower(fdst.short_text) like lower('%'||:short_text||'%'))
Text contains
lower(x.text) like lower('%'||:text||'%')
Creation Date from
fd.creation_date>=:date_from
DateTime
Creation Date to
fd.creation_date<:date_to+1
DateTime
Created within days
fd.creation_date>=sysdate-:days
Number
Created By
fd.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:user_name)
LOV
Show Attachment Objects
Y
LOV
Attached to Application
fd.document_id in (
select
fad.document_id
from
fnd_document_entities_vl fdev,
fnd_attached_documents fad
where
fdev.application_name=:application and
fdev.data_object_code=fad.entity_name)
LOV
Document Entity
fd.document_id in (
select
fad.document_id
from
fnd_document_entities_vl fdev,
fnd_attached_documents fad
where
fdev.user_entity_name=:user_entity_name and
fdev.data_object_code=fad.entity_name)
LOV
Attached to Application
Y
Document Entity
Y