Short text attachments

SELECT st.short_text INTO l_short_text
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fdtl
--fnd_attached_docs_form_vl fadf
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'OEXOETEL'
--AND fndattdoc.entity_name = 'OE_ORDER_HEADERS'
AND fnddoc.media_id = st.media_id(+)
AND fdtl.category_id = fndcatusg.category_id
--AND fdtl.user_name IN ('Sales Order Acknowledgement - Header')
AND fdtl.user_name = :P_FOOTER_ATTACH
AND fndattdoc.pk1_value = :OE_ORDER_HEADERS_ALL.HEADER_ID
/*AND fadf.pk1_value = fndattdoc.pk1_value
AND fndattfn.function_name = fadf.function_name
AND fadf.document_id = fnddoc.document_id
AND fndcatusg.format = 'F'
AND fadf.function_type = 'R'*/
AND fdtl.LANGUAGE = USERENV ('LANG');
*****************************************************************
Below query is Tuned
*****************************************************************

select
-- pk1_value ftr_header_id,
pk1_value actheadnote_id,
fdst.short_text actheadnote,
fdlt.long_text actheadnote_long,
fdc.user_name ftr_headuse_ord,
TO_NUMBER ( fdc.attribute1 ) ftr_headuseseq_ord,
datatype_id ftr_note_datatype_id,
fd.datatype_id actheadnote_datatype_id
from fnd_attachment_functions faf,
fnd_document_categories_vl fdc,
fnd_doc_category_usages fdcu,
fnd_documents_vl fd,
fnd_attached_documents fad,
fnd_documents_long_text fdlt,
fnd_documents_short_text fdst
where faf.attachment_function_id = fdcu.attachment_function_id
and fdcu.category_id = fd.category_id
and fdc.category_id = fd.category_id
and fd.document_id = fad.document_id
and faf.function_name = :P_FUNCTION_NAME
--and faf.function_name = 'OEXOEORD'
and fad.entity_name = 'OE_ORDER_HEADERS'
and fd.datatype_id in (1,2)
and fd.media_id=fdst.media_id(+)
and fd.media_id=fdlt.media_id(+)
and fdc.user_name <> 'AU Header Comments' ---- to hide attachments URL from XMl reports
and fdc.user_name <> 'Internal'
and faf.function_type='R'
AND FDCU.format = 'F'
order by seq_num

To giving URL link for the Attachments

function CF_attach return Char is
l_gfm_id NUMBER;
gfm_agent VARCHAR2 (255);
l_url VARCHAR2 (1000);
MED_ID number;
begin

SELECT DISTINCT DT.MEDIA_ID INTO MED_ID
FROM FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT
WHERE((entity_name = 'PO_HEAD' and pk1_value = :POH_PO_HEADER_ID and
pk2_value = '1') OR
(entity_name = 'PO_HEADERS' and pk1_value = :POH_PO_HEADER_ID) OR
(entity_name = 'PO_VENDORS' and pk1_value = :POH_VENDOR_ID))
AND AD.DOCUMENT_ID=DT.DOCUMENT_ID
AND DT.LANGUAGE = USERENV('LANG')
AND ROWNUM=1;

gfm_agent := fnd_web_config.gfm_agent;
--DBMS_OUTPUT.put_line (gfm_agent);
l_gfm_id := MED_ID; --media id
--DBMS_OUTPUT.put_line (l_gfm_id); -- MEDIA_ID
l_url := fnd_gfm.construct_download_url (gfm_agent, l_gfm_id, FALSE);
--DBMS_OUTPUT.put_line (l_url);

return l_url;

EXCEPTION WHEN OTHERS THEN
RETURN(NULL);
end;

Print Bill To Customer address from Order Management

SELECT hl.address1, hl.address2, hl.address3,
hl.address4, hl.city, hl.postal_code
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE hcs.site_use_code = 'BILL_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = oe_order_headers_all.invoice_to_org_id;

Print Ship To Customer Address from Order Management

SELECT hl.address1, hl.address2, hl.address3,
hl.address4, hl.city, hl.postal_code,hl.STATE,hp.party_name
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE hcs.site_use_code = 'SHIP_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = wsh_delivery_details.ship_from_location_id;-->Take this coloumn from the table (wsh_delivery_details)

or use the below query

SELECT hp.party_name,hl.address1, hl.address2, hl.address3,
hl.address4,hl.PROVINCE, hl.city,hl.STATE, hl.postal_code,hl.COUNTRY,hcs.site_use_code
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE 1=1--hcs.site_use_code = 'BILL_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
--AND hca.cust_account_id = 2231634 --oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = (SELECT (ship_to_org_id) or (invoice_to_org_id) FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID=
(select DISTINCT SOURCE_HEADER_ID from WSH_DELIVERABLES_V WHERE DELIVERY_ID=:P_DELIVERY_ID))-->use either (ship_to_org_id) or (invoice_to_org_id)

Print the Inventory org Address

select * from hr_locations_v hlv, hr_all_organization_units hou
WHERE hlv.inventory_organization_id = hou.organization_id
AND hlv.location_id = hou.location_id
AND hou.organization_id = :p_mfg_org;

************************************************

select hla.description,
hla.address_line_1||hla.address_line_2 address1,
hla.town_or_city||hla.region_2||' '||hla.postal_code address2,
ft.ISO_territory_code address3,
'(Tel) + '||hla.telephone_number_1 Tel,
' (Fax) + '|| hla.telephone_number_2 Fax
from hr_locations_all hla, fnd_territories ft
where hla.inventory_organization_id=:P_MFG_ORG
and ft.territory_code=hla.country
and hla.inactive_date is null;