SQL Query Supplier Details

SELECT *
FROM (
  (SELECT POV.VENDOR_ID VENDOR_ID,
    POV.VENDOR_NAME SUPPLIER_NAME ,
    POV.SEGMENT1 SUPPLIER_NUMBER ,
    POV.VENDOR_NAME_ALT ALTERNAETE_NAME ,
    POV.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUMBER ,
    POV.ATTRIBUTE3 VENDOR_TYPE ,
    PLC.DISPLAYED_FIELD SUPPLIER_TYPE ,
    POVSA.VENDOR_SITE_CODE SITE_NAME ,
    FT.TERRITORY_SHORT_NAME COUNTRY ,
    POVSA.ADDRESS_LINE1 ADDRESS_LINE1 ,
    POVSA.ADDRESS_LINE2 ADDRESS_LINE2 ,
    POVSA.ADDRESS_LINE3 ADDRESS_LINE3 ,
    POVSA.ADDRESS_LINE4 ADDRESS_LINE4 ,
    POVSA.CITY CITY ,
    POVSA.STATE STATE ,
    POVSA.ZIP POSTAL_CODE ,
    POVSA.PROVINCE PROVINCE ,
    POVSA.COUNTY COUNTY ,
    POVSA.LANGUAGE LANGUAGE ,
    POVSA.PHONE COMMUNICATION_VOICE ,
    POVSA.FAX COMMUNICATION_FAX ,
    POVSA.EMAIL_ADDRESS COMMUNICATION_EMAIL ,
    POVC.LAST_NAME CONTACT_NAME ,
    POVC.PHONE CONTACT_TELEPHONE ,
    POVC.FAX CONTACT_FAX ,
    POVC.EMAIL_ADDRESS CONTACT_EMAIL ,
    GCC.SEGMENT1
    ||'-'
    ||GCC.SEGMENT2
    ||'-'
    ||GCC.SEGMENT3
    ||'-'
    ||GCC.SEGMENT4
    ||'-'
    ||GCC.SEGMENT5
    ||'-'
    ||GCC.SEGMENT6
    ||'-'
    ||GCC.SEGMENT7
    ||'-'
    ||GCC.SEGMENT8
    ||'-'
    ||GCC.SEGMENT9
    ||'-'
    ||GCC.SEGMENT10 LIABILITY_ACCOUNT ,
    DECODE(POVSA.MATCH_OPTION,'P','Purchase Order','R','Receipt') INVOICE_MATCH_OPTION ,
    POVSA.HOLD_UNMATCHED_INVOICES_FLAG HOLD_UNMATCHED_INVOICE ,
    (SELECT APT.NAME
    FROM AP_TERMS_TL APT
    WHERE APT.TERM_ID = POVSA.TERMS_ID
    AND APT.LANGUAGE  = 'US'
    )PAYMENT_TERMS ,
    POVSA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY ,
    POVSA.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD ,
    ABA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME ,
    ABA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER ,
    ABB.BANK_NAME BANK_NAME,
    ABB.BANK_NUMBER BANK_NUMBER,
    ABB.BANK_BRANCH_NAME BANK_BRANCH_NAME,
    ABB.BANK_NUM BANK_BRANCH_NUMBER,
    FTB.TERRITORY_SHORT_NAME BANK_COUNTRY,
    POVSA.VAT_CODE INVOICE_TAX_CODE ,
    POV.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUMBER1 ,
    POVSA.TAX_REPORTING_SITE_FLAG INCOME_TAX_REPORTING_SITE ,
    HR_SHIPTO.LOCATION_CODE SHIP_TO_LOCATION ,
    HR_BILLTO.LOCATION_CODE BILL_TO_LOCATION,
    POV.CREATION_DATE CREATION_DATE,
    POV.CREATED_BY CREATED_BY,
    POV.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    POV.LAST_UPDATED_BY LAST_UPDATED_BY,
    POV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
  FROM APPS.PO_VENDORS POV,
    APPS.PO_VENDOR_SITES_ALL POVSA ,
    APPS.FND_TERRITORIES_TL FT ,
    APPS.PO_VENDOR_CONTACTS POVC ,
    APPS.GL_CODE_COMBINATIONS GCC ,
    APPS.HR_LOCATIONS_ALL HR_SHIPTO ,
    APPS.HR_LOCATIONS_ALL HR_BILLTO ,
    APPS.PO_LOOKUP_CODES PLC ,
    APPS.AP_BANK_ACCOUNT_USES_ALL ABAU,
    APPS.AP_BANK_ACCOUNTS_ALL ABA,
    APPS.AP_BANK_BRANCHES ABB,
    APPS.FND_TERRITORIES_TL FTB
  WHERE POV.VENDOR_ID               = POVSA.VENDOR_ID
  AND FT.TERRITORY_CODE             = POVSA.COUNTRY
  AND FT.LANGUAGE                   = 'US'
  AND POVC.VENDOR_SITE_ID(+)        = POVSA.VENDOR_SITE_ID
  AND GCC.CODE_COMBINATION_ID       = POVSA.ACCTS_PAY_CODE_COMBINATION_ID
  AND HR_SHIPTO.LOCATION_ID(+)      = POVSA.SHIP_TO_LOCATION_ID
  AND HR_BILLTO.LOCATION_ID(+)      = POVSA.BILL_TO_LOCATION_ID
  AND PLC.LOOKUP_CODE(+)            = POV.VENDOR_TYPE_LOOKUP_CODE
  AND PLC.LOOKUP_TYPE(+)            = 'VENDOR TYPE'
  AND ABAU.VENDOR_ID                = POVSA.VENDOR_ID(+)
  AND ABAU.VENDOR_SITE_ID           = POVSA.VENDOR_SITE_ID(+)
  AND ABAU.EXTERNAL_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID(+)
  AND ABA.BANK_BRANCH_ID            = ABB.BANK_BRANCH_ID
  AND FTB.TERRITORY_CODE(+)         = ABB.COUNTRY
  AND FTB.LANGUAGE(+)               = 'US'
  AND POVSA.ORG_ID                  = 2659
  )
UNION ALL
  (SELECT POV.VENDOR_ID VENDOR_ID,
    POV.VENDOR_NAME SUPPLIER_NAME ,
    POV.SEGMENT1 SUPPLIER_NUMBER ,
    POV.VENDOR_NAME_ALT ALTERNAETE_NAME ,
    POV.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUMBER ,
    POV.ATTRIBUTE3 VENDOR_TYPE ,
    PLC.DISPLAYED_FIELD SUPPLIER_TYPE ,
    POVSA.VENDOR_SITE_CODE SITE_NAME ,
    FT.TERRITORY_SHORT_NAME COUNTRY ,
    POVSA.ADDRESS_LINE1 ADDRESS_LINE1 ,
    POVSA.ADDRESS_LINE2 ADDRESS_LINE2 ,
    POVSA.ADDRESS_LINE3 ADDRESS_LINE3 ,
    POVSA.ADDRESS_LINE4 ADDRESS_LINE4 ,
    POVSA.CITY CITY ,
    POVSA.STATE STATE ,
    POVSA.ZIP POSTAL_CODE ,
    POVSA.PROVINCE PROVINCE ,
    POVSA.COUNTY COUNTY ,
    POVSA.LANGUAGE LANGUAGE ,
    POVSA.PHONE COMMUNICATION_VOICE ,
    POVSA.FAX COMMUNICATION_FAX ,
    POVSA.EMAIL_ADDRESS COMMUNICATION_EMAIL ,
    POVC.LAST_NAME CONTACT_NAME ,
    POVC.PHONE CONTACT_TELEPHONE ,
    POVC.FAX CONTACT_FAX ,
    POVC.EMAIL_ADDRESS CONTACT_EMAIL ,
    GCC.SEGMENT1
    ||'-'
    ||GCC.SEGMENT2
    ||'-'
    ||GCC.SEGMENT3
    ||'-'
    ||GCC.SEGMENT4
    ||'-'
    ||GCC.SEGMENT5
    ||'-'
    ||GCC.SEGMENT6
    ||'-'
    ||GCC.SEGMENT7
    ||'-'
    ||GCC.SEGMENT8
    ||'-'
    ||GCC.SEGMENT9
    ||'-'
    ||GCC.SEGMENT10 LIABILITY_ACCOUNT ,
    DECODE(POVSA.MATCH_OPTION,'P','Purchase Order','R','Receipt') INVOICE_MATCH_OPTION ,
    POVSA.HOLD_UNMATCHED_INVOICES_FLAG HOLD_UNMATCHED_INVOICE ,
    (SELECT APT.NAME
    FROM AP_TERMS_TL APT
    WHERE APT.TERM_ID = POVSA.TERMS_ID
    AND APT.LANGUAGE  = 'US'
    )PAYMENT_TERMS ,
    POVSA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY ,
    POVSA.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD ,
    NULL BANK_ACCOUNT_NAME ,
    NULL BANK_ACCOUNT_NUMBER ,
    NULL BANK_NAME,
    NULL BANK_NUMBER,
    NULL BANK_BRANCH_NAME,
    NULL BANK_BRANCH_NUMBER,
    NULL BANK_COUNTRY,
    POVSA.VAT_CODE INVOICE_TAX_CODE ,
    POV.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUMBER1 ,
    POVSA.TAX_REPORTING_SITE_FLAG INCOME_TAX_REPORTING_SITE ,
    HR_SHIPTO.LOCATION_CODE SHIP_TO_LOCATION ,
    HR_BILLTO.LOCATION_CODE BILL_TO_LOCATION,
    POV.CREATION_DATE CREATION_DATE,
    POV.CREATED_BY CREATED_BY,
    POV.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    POV.LAST_UPDATED_BY LAST_UPDATED_BY,
    POV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
  FROM APPS.PO_VENDORS POV,
    APPS.PO_VENDOR_SITES_ALL POVSA ,
    APPS.FND_TERRITORIES_TL FT ,
    APPS.PO_VENDOR_CONTACTS POVC ,
    APPS.GL_CODE_COMBINATIONS GCC ,
    APPS.HR_LOCATIONS_ALL HR_SHIPTO ,
    APPS.HR_LOCATIONS_ALL HR_BILLTO ,
    APPS.PO_LOOKUP_CODES PLC
  WHERE POV.VENDOR_ID          = POVSA.VENDOR_ID
  AND FT.TERRITORY_CODE        = POVSA.COUNTRY
  AND FT.LANGUAGE              = 'US'
  AND POVC.VENDOR_SITE_ID(+)   = POVSA.VENDOR_SITE_ID
  AND GCC.CODE_COMBINATION_ID  = POVSA.ACCTS_PAY_CODE_COMBINATION_ID
  AND HR_SHIPTO.LOCATION_ID(+) = POVSA.SHIP_TO_LOCATION_ID
  AND HR_BILLTO.LOCATION_ID(+) = POVSA.BILL_TO_LOCATION_ID
  AND PLC.LOOKUP_CODE(+)       = POV.VENDOR_TYPE_LOOKUP_CODE
  AND PLC.LOOKUP_TYPE(+)       = 'VENDOR TYPE'
  AND POVSA.ORG_ID             = 2659
  AND NOT EXISTS
    (SELECT 1
    FROM APPS.AP_BANK_ACCOUNT_USES_ALL
    WHERE VENDOR_SITE_ID = POVSA.VENDOR_SITE_ID
    )
  ) )
ORDER BY VENDOR_ID;

SQL Scripts to get PO Quotations Details

---To extact PO Quotation  headers
SELECT pha.segment1 Documet_number
      ,pha.type_lookup_code document_type_code
      ,pha.quote_type_lookup_code Document_Sub_Type
      ,pv.segment1 Vendor_Number
      ,pvs.Vendor_Site_Code
      ,s_loc.location_code Ship_to_Location
      ,b_loc.location_code Bill_to_location 
      ,pap.employee_number Buyer
      ,pha.currency_code
      ,decode(status_lookup_code,'I','INCOMPLETE','A','APPROVED','C','CLOSED') Approva_status
      ,approved_date
      ,pha.org_id
      ,pha.rate_type
      ,pha.rate_date
      ,pha.creation_date
      ,pha.created_by
FROM apps.po_headers_all pha,
     apps.Po_vendors pv,
     apps.po_vendor_sites_all pvs,
     apps.per_all_people_f  pap,
     apps.hr_locations_all b_loc,
     apps.hr_locations_all s_loc
WHERE pha.vendor_id=pv.vendor_id
AND   pvs.vendor_site_id=pha.vendor_site_id
AND pap.person_id=pha.agent_id
AND b_loc.location_id=pha.bill_to_location_id
AND  s_loc.location_id=pha.ship_to_location_id
AND pha.type_lookup_code='QUOTATION'
AND pha.org_id            =2661
order by 1;

--To find inventory organizations for OU
select organization_id,organization_name from apps.org_organization_definitions where operating_unit=2661;
--To extact PO Quotation Lines
SELECT pha.segment1 Documet_number
      ,pla.line_num
      ,plt.line_type
      ,msi.segment1 Item
      ,pla.unit_meas_lookup_code Unit_Of_Measure
      ,pla.unit_price
      ,pla.vendor_product_num Supplier_Item
      ,pla.creation_date
      ,pla.created_by
FROM apps.po_lines_all pla
    ,apps.po_headers_all pha
   ,(select distinct segment1,inventory_item_id from apps.MTL_SYSTEM_ITEMS_B msi where organization_id in (3519,3619)) msi
   ,apps.po_line_types_tl plt
WHERE pha.po_header_id     =pla.po_header_id
AND pha.type_lookup_code='QUOTATION'
AND plt.line_type_id=pla.line_type_id
and plt.language='US'
AND msi.inventory_item_id=pla.item_id
AND pha.org_id          =2661
order by 1,2 asc;


--To extact Po Quotation line  Breaks
SELECT pha.segment1 Documet_number ,
  pla.line_num ,
  plla.shipment_num ,
  plla.shipment_type,
  msi.segment1 Item ,
  plla.quantity ,
  plla.PRICE_OVERRIDE Unit_Price ,
  plla.unit_meas_lookup_code Unit_Of_Measure ,
  (SELECT ood.organization_code
  FROM apps.ORG_ORGANIZATION_DEFINITIONS ood
  WHERE ood.organization_id=plla.ship_to_organization_id
  ) ship_to_organization_code ,
  plla.start_date ,
  plla.end_date ,
  plla.po_header_id ,
  plla.po_line_id,
  plla.creation_date,
  plla.created_by
FROM apps.po_line_locations_all plla ,
  apps.po_lines_all pla ,
  apps.po_headers_all pha ,
  (select distinct segment1,inventory_item_id from apps.MTL_SYSTEM_ITEMS_B msi where organization_id in (3519,3619)) msi
WHERE plla.po_line_id=pla.po_line_id
AND pha.po_header_id =pla.po_header_id
AND pha.type_lookup_code='QUOTATION'
AND msi.inventory_item_id=pla.item_id
AND pha.org_id           =2661
ORDER BY 1,2,3 ASC;

 

SQL SCripts to get customer , Profiles and contacts details

--To Extract Customer header and details
SELECT distinct hca.orig_system_reference "Orig System Customer  Ref" ,
  hcs.ORIG_SYSTEM_REFERENCE "Orig System address  Ref" ,
  HP.PARTY_NAME CUSTOMER_NAME ,
  DECODE(hca.customer_type,'I','Internal','R','Extrenal',hca.customer_type) Customer_Type,
  hca.status,
  hp.ORGANIZATION_NAME_PHONETIC customer_alt_name,
  hl.country ,
  hl.address1 ,
  hl.address2 ,
  hl.address3 ,
  hl.address4 ,
  hl.ADDRESS_LINES_PHONETIC Alternate_Name ,
  hl.city ,
  hl.state ,
  HL.POSTAL_CODE,
  HCSU.SITE_USE_CODE USAGE ,
  HCS.LANGUAGE ,
  HCSU.LOCATION ,
  HCSU.PRIMARY_FLAG ,
  HCSU.TAX_CODE ,
  HCSU.TAX_REFERENCE "Tax Registration Number" ,
  (SELECT SEGMENT1
    ||'-'
    ||SEGMENT2
    ||'-'
    ||SEGMENT3
    ||'-'
    ||SEGMENT4
    ||'-'
    ||SEGMENT5
    ||'-'
    ||SEGMENT6
    ||'-'
    ||SEGMENT7
    ||'-'
    ||SEGMENT8
    ||'-'
    ||SEGMENT9
    ||'-'
    ||SEGMENT10
  FROM APPS.GL_CODE_COMBINATIONS
  WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REC
  )"Accounts Receivable" ,
  (SELECT SEGMENT1
    ||'-'
    ||SEGMENT2
    ||'-'
    ||SEGMENT3
    ||'-'
    ||SEGMENT4
    ||'-'
    ||SEGMENT5
    ||'-'
    ||SEGMENT6
    ||'-'
    ||SEGMENT7
    ||'-'
    ||SEGMENT8
    ||'-'
    ||SEGMENT9
    ||'-'
    ||SEGMENT10
  FROM APPS.GL_CODE_COMBINATIONS
  WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REV
  ) "Accounts Revenue" ,
  (SELECT SEGMENT1
    ||'-'
    ||SEGMENT2
    ||'-'
    ||SEGMENT3
    ||'-'
    ||SEGMENT4
    ||'-'
    ||SEGMENT5
    ||'-'
    ||SEGMENT6
    ||'-'
    ||SEGMENT7
    ||'-'
    ||SEGMENT8
    ||'-'
    ||SEGMENT9
    ||'-'
    ||SEGMENT10
  FROM APPS.GL_CODE_COMBINATIONS
  WHERE CODE_COMBINATION_ID=HCSU.GL_ID_FREIGHT
  )"Accounts  Freight" ,
  HCSU.SHIP_VIA SHIPPING_METHOD ,
    hca.account_name,
   (SELECT DISTINCT NAME
  FROM APPS.RA_SALESREPS_ALL
  WHERE SALESREP_ID=HCSU.PRIMARY_SALESREP_ID
  ) "Sales Person" ,
   HCSU.FREIGHT_TERM ,
    (SELECT DISTINCT NAME
  FROM APPS.SO_PRICE_LISTS
  WHERE PRICE_LIST_ID=HCSU.PRICE_LIST_ID
  ) "Price List" ,
   HCSU.FOB_POINT ,
  (SELECT NAME FROM APPS.RA_TERMS WHERE TERM_ID=HCSU.PAYMENT_TERM_ID
  ) "Payment Term" ,
   (SELECT DISTINCT NAME
  FROM APPS.OE_TRANSACTION_TYPES_TL
  WHERE UPPER(TRANSACTION_TYPE_ID)=HCSU.ORDER_TYPE_ID
  ) "Order Type",
  hps.addressee,
  hcsu.tax_classification,
  HCSU.global_attribute_category Context_value,
  hcsu.attribute1 recipt_Ack,
  hca.account_number Customer_number,
  hp.party_id,
  hps.party_site_id,
  hps.location_id,
  hp.party_number ,
  HCS.org_id,
  hca.creation_date,
  hca.created_by
FROM apps.HZ_PARTIES hp ,
  apps.HZ_LOCATIONS hl ,
  apps.hz_party_sites hps ,
  apps.hz_cust_accounts_all hca,
  APPS.HZ_CUST_ACCT_SITES_ALL HCS ,
  APPS.HZ_CUST_SITE_USES_ALL HCSU
WHERE 1           =1
AND hps.party_id  =hp.party_id
AND hca.party_id  =hp.party_id
AND hcs.party_site_id=hps.party_site_id
AND HCS.CUST_ACCOUNT_ID   =HCA.CUST_ACCOUNT_ID
AND HCS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID
AND hl.location_id=hps.location_id
AND HCS.org_id=2661
--AND hca.orig_system_reference like 'SP%'
ORDER BY hca.ORIG_SYSTEM_REFERENCE ;

--- To Extract Customer profiles
SELECT DISTINCT  hca.orig_system_reference Orig_system_customer_ref,
  hcsa.orig_system_reference Orig_system_address_ref,
  hca.account_number Customer_Number,
  (SELECT NAME
  FROM APPS.HZ_CUST_PROFILE_CLASSES
  WHERE profile_class_id=hpc.profile_class_id
  ) customer_profile_class_name ,
  (SELECT name FROM apps.AR_COLLECTORS WHERE collector_id=hpc.COLLECTOR_ID
  ) Collector_Name ,
  CREDIT_HOLD ,
  CREDIT_CHECKING ,
  (SELECT name FROM apps.RA_TERMS WHERE term_id=STANDARD_TERMS
  ) Term_Name ,
  hpc.SEND_STATEMENTS "Send Statement" ,
  (SELECT name
  FROM apps.AR_STATEMENT_CYCLES as1
  WHERE as1.STATEMENT_CYCLE_ID=hpc.STATEMENT_CYCLE_ID
  ) Cycle ,
  DUNNING_LETTERS Send_Letters,
  (SELECT name
  FROM apps.ar_dunning_letter_sets adl
  WHERE adl.dunning_letter_set_id=hpc.dunning_letter_set_id
  ) dunning_letter_set_name ,
  (SELECT name
  FROM apps.RA_GROUPING_RULES
  WHERE GROUPING_RULE_ID=hpc.GROUPING_RULE_ID
  ) "Grouping Rule" ,
  cpa.OVERALL_CREDIT_LIMIT ,
  cpa.trx_credit_limit ,
  cpa.CURRENCY_CODE,
  credit_balance_statements,
  hpc.cust_account_id,
  hpc.site_use_id,
  hcsa.org_id,
  hpc.creation_date,
  hpc.created_by
FROM apps.HZ_CUSTOMER_PROFILES hpc ,
  apps.hz_cust_accounts_all hca,
  apps.HZ_CUST_ACCT_SITES_ALL hcsa,
  apps.hz_cust_site_uses_all sit_use,
  APPS.HZ_CUST_PROFILE_AMTS CPA
WHERE 1                      =1
AND hca.cust_account_id      =hpc.cust_account_id
AND sit_use.cust_acct_site_id=hcsa.cust_acct_site_id(+)
AND CPA.cust_account_profile_id=hpc.cust_account_profile_id
AND sit_use.site_use_id(+)=hpc.site_use_id
AND hcsa.org_id=2661
--AND hca.orig_system_reference LIKE'SP%'
ORDER BY hca.orig_system_reference;
--To Extract Customer Contact Phones
select *from APPS.HZ_CONTACT_POINTS;SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
 acar.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_CONTACT_REF ,
 hca.orig_system_reference Orig_system_Customer_ref ,
  hcsa.orig_system_reference Orig_system_address_ref ,
  hca.account_number Customer_Number,
  hcp.PHONE_NUMBER Telephone_Numbe ,
  hcp.PHONE_EXTENSION Extension ,
  hcp.EMAIL_ADDRESS Mail ,
  hcp.PHONE_LINE_TYPE Type ,
  hcp.PHONE_AREA_CODE Telephone_Area_Code ,
  hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
  hcp.CONTACT_POINT_TYPE Contact_Point_Type, 
  hp.person_first_name  First_Name,
  hp.person_last_name  Last_Name,
  acar.cust_account_id ,
  acar.cust_acct_site_id ,
  hcsa.org_id,
  hcp.creation_date,
  hcp.created_by
  FROM apps.HZ_CUST_ACCOUNT_ROLES acar ,
  apps.hz_cust_accounts hca,
  apps.HZ_CUST_ACCT_SITES_ALL hcsa,
  apps.hz_parties hp,
  apps.hz_relationships hr,
  APPS.HZ_CONTACT_POINTS hcp
WHERE 1                   =1
AND hca.cust_account_id  =acar.cust_account_id
AND hcsa.cust_acct_site_id(+)=acar.Cust_acct_site_id
AND hp.party_id  = hr.object_id       
and acar.party_id = hr.party_id
and hr.party_id=hcp.owner_table_id
and hcp.owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES')
and hr.relationship_code = 'CONTACT'
--AND hca.ORIG_SYSTEM_REFERENCE like 'SP%'
and hcsa.org_id=2661;

-- To Extract Customers Only Telephone references which are not having contact reference
SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
  hcp.PHONE_NUMBER Telephone_Numbe ,
  hcp.PHONE_EXTENSION Extension ,
  hcp.EMAIL_ADDRESS Mail ,
  hcp.PHONE_LINE_TYPE Type ,
  hcp.PHONE_AREA_CODE Telephone_Area_Code ,
  hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
  hcp.CONTACT_POINT_TYPE Contact_Point_Type ,
  hcp.creation_date,
  hcp.created_by
  FROM
    APPS.HZ_CONTACT_POINTS hcp
WHERE 1                   =1
AND hcp.ORIG_SYSTEM_REFERENCE like 'SP%%'
and not exists (select 1 from hz_relationships hr where  hr.party_id=hcp.owner_table_id);
---Customer relationships
SELECT CUST_ACCOUNT_ID ,
  (SELECT ORIG_SYSTEM_REFERENCE
  FROM hz_cust_accounts_all
  WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
  ) ORIG_SYSTEM_REFERENCE,
  (SELECT ACCOUNT_NUMBER
  FROM hz_cust_accounts_all
  WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
  ) CUSTOMER_NUMBER,
  RELATED_CUST_ACCOUNT_ID,
  (SELECT ORIG_SYSTEM_REFERENCE
  FROM hz_cust_accounts_all
  WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
  ) RELATED_ORIG_SYSTEM_REFERENCE,
  (SELECT ACCOUNT_NUMBER
  FROM hz_cust_accounts_all
  WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
  ) RELATED_CUSTOMER_NUMBER,
  RELATIONSHIP_TYPE,
  comments,
  CUSTOMER_RECIPROCAL_FLAG,
  BILL_TO_FLAG,
  SHIP_TO_FLAG,
  hcar.creation_date,
  hcar.created_by
FROM apps.hz_cust_acct_relate_all HCAR
where org_id=2661;

SQL Script to get invoice ship to use id and respsctive order ship to use id

select distinct trx.customer_trx_id
          ,hca.cust_account_id
          ,trx.trx_number
          ,case trx_lines.SALES_ORDER_SOURCE
                when 'ORDER ENTRY' then
                (select ship_to_org_id from apps.oe_order_lines_all where
                line_id=trx_lines.interface_line_attribute6)
                else
                null
end order_ship_to_use_id
,trx.SHIP_TO_site_use_id invoice_ship_to_use_id
   from ra_customer_trx_all trx
        ,hz_cust_accounts_all hca
        ,ra_customer_trx_lines_all trx_lines
         where hca.cust_account_id=trx.ship_to_customer_id
   and trx_lines.customer_trx_id=trx.customer_trx_id
   and TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 is not null
   and trx.org_id=2661
   and trx_number='1000089'
      ;

SQL Script to pick responsibilities and other details

Responsibility and menu exclusions
----------------------------------------------------------------------------
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,frf.rule_type Exclusions_Type
   ,fff.user_function_name Exclusions
   ,fff.function_name function_name
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg,
  apps.fnd_form_functions_vl fff,
  apps.fnd_resp_functions frf
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND frf.responsibility_id(+) =fr.responsibility_id
AND frf.action_id            =fff.function_id(+)
AND frt.responsibility_name LIKE '%XXXtest%'
AND rule_type='F'
UNION ALL
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,frf.rule_type Exclusions_Type
  ,FMV.MENU_NAME Exclusions
  ,null function_name
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg,
  apps.fnd_resp_functions frf,
  APPS.FND_MENUS_VL FMV
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND frf.responsibility_id(+) =fr.responsibility_id
AND frf.action_id            =FMV.MENU_id(+)
AND frt.responsibility_name LIKE '%XXXtest%'
AND rule_type='M'
 UNION ALL
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,NULL Exclusions_Type
  ,null function_name
  ,NULL Exclusions
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg  
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND fr.responsibility_id NOT IN(SELECT responsibility_id FROM  APPS.fnd_resp_functions)
AND frt.responsibility_name LIKE '%XXXtest%'

------------------------------------------------------------------------------------------------------

Responsibility and Profiles

select distinct
 b.user_profile_option_name "Profile option  Name"
 , a.profile_option_name "Short Name"
 , decode(to_char(c.level_id),'10001','Site'
 ,'10002','Application'
 ,'10003','Responsibility'
 ,'10004','User'
 ,'Unknown') "Level"
 , decode(to_char(c.level_id),'10001','Site'
 ,'10002',nvl(h.application_short_name,to_char(c.level_value))
 ,'10003',nvl(g.responsibility_name,to_char(c.level_value))
 ,'10004',nvl(e.user_name,to_char(c.level_value))
 ,'Unknown') "Level Value"
 , c.PROFILE_OPTION_VALUE "Profile Value"
 --, c.profile_option_id "Profile ID"
 --, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
 --, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
 from
 apps.fnd_profile_options a
 , apps.FND_PROFILE_OPTIONS_VL b
 , apps.FND_PROFILE_OPTION_VALUES c
 --, apps.FND_USER d
 , apps.FND_USER e
 , apps.FND_RESPONSIBILITY_VL g
 , apps.FND_APPLICATION h
 where 1=1
 --a.application_id = nvl(401, a.application_id)
 --and a.profile_option_name = nvl('INV', a.profile_option_name)
 --b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
 and a.profile_option_name = b.profile_option_name
 and a.profile_option_id = c.profile_option_id
 and a.application_id = c.application_id
 --and c.last_updated_by = d.user_id(+)
 and c.level_value = e.user_id(+)
 and c.level_value = g.responsibility_id(+)
 and c.level_value = h.application_id(+)
 and g.responsibility_name LIKE '%XXXtest%'--='Bills of Material PXR DE Eching' --
 and c.level_id='10003' 

SQL Scripts on Oracle Apps Attachments

What is attachment in oracle application?
The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions.
Where to find an attachment?
There is an attachment icon in the oracle application toolbar that indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment.
Attachment types:
An attached document can be:
1] Short Text
Text stored in the database containing less than 2000 characters.
2] Long Text
Text stored in the database containing 2000 characters or more.
3] Image
An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif, pcd, pcx, pict, ras, and tif.
4] OLE Object
An OLE Object that requires other OLE server applications to view, such as Microsoft Word or Microsoft Excel.
5] Web Page
A URL reference to a web page which you can view with your web browser.

Tables Involved:
For Importing Attachments in oracle application one has to populate following tables.
1. FND_DOCUMENTS
2. FND_ATTACHED_DOCUMENTS
3. FND_DOCUMENTS_TL
4. FND_DOCUMENT_DATATYPES.
5. FND_DOCUMENT_CATEGORIES
6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).
7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).
8. FND_DOCUMENTS_LONG_RAW
9. FND_LOBS (File type attachments).
FND_DOCUMENTS:
FND_DOCUMENTS stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books, 3=Business unit,4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).
Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE object), image type, and storage type (STORAGE_TYPE, where 1=stored in the database, 2=stored in the file system).
The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as templates.

Queries:
1] To find all Long Text attachments:

SELECT
        FAD.SEQ_NUM "Seq Number",
        FDAT.USER_NAME "Data Type",
        FDCT.USER_NAME "Category User Name",
        FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
        FDET.USER_ENTITY_NAME "User Entity",
        FD.DOCUMENT_ID "Document Id",
        FAD.ENTITY_NAME "Entity Name",
        FD.MEDIA_ID "Media Id",
        FD.URL "Url",
        FDT.TITLE "Title",
        FDLT.LONG_TEXT "Attachment Text"
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_LONG_TEXT FDLT
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDLT.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = 'LONG_TEXT';

2] To find all Short Text attachments:

SELECT
        FAD.SEQ_NUM "Seq Number",
        FDAT.USER_NAME "Data Type",
        FDCT.USER_NAME "Category User Name",
        FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
        FDET.USER_ENTITY_NAME "User Entity",
        FD.DOCUMENT_ID "Document Id",
        FAD.ENTITY_NAME "Entity Name",
        FD.MEDIA_ID "Media Id",
        FD.URL "Url",
        FDT.TITLE "Title",
        FDST.SHORT_TEXT "Attachment Text"
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_SHORT_TEXT FDST
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDST.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = 'SHORT_TEXT';

---------------------------------------------------------------------------------------------------------------------------------------------

The following SQL gives the details of all attachments on PO headers. To get only attachments for a particular PO, add a line in the where clause, AND fad.pk1_value = <<PO_HEADER_ID>>

-- Get the attachment data from database.
SELECT fdl.description, fl.file_id, fdl.file_name, fad.pk1_value "Header ID", fl.upload_date "File added on", fl.file_format, fu.description created_by
  FROM fnd_lobs fl, fnd_attached_documents fad, fnd_documents_tl fdl, fnd_user fu
 WHERE 1 = 1 AND fdl.document_id = fad.document_id AND fdl.media_id = fl.file_id AND fad.entity_name = 'PO_HEADERS'
-- AND fdl.language=l_language
-- AND fl.file_name = l_filename
AND fu.user_id = fdl.created_by

-----------------------------------------------------------------------------------------------------------------------------------------------
Attachment upload through API:
Attachments can also be uploaded through an oracle provided API called  FND_ATTACHED_DOCUMENTS_PKG.
It consist of three procedures
1)  Insert Row
2)  Update Row
3)  Lock Row
Names of these procedures are self explanatory. insert row is used to insert a new row for attachment data, update row is used to update existing row for a particular row and Lock Row is used to lock a existing row for further modification.

Attachments API - FND_DOCUMENTS_PKG, FND_ATTACHED_DOCUMENTS_PKG
Attachments API - FND_DOCUMENTS_PKG, FND_ATTACHED_DOCUMENTS_PKG


Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments (If exists)) in Oracle Apps. To understand the below API and what it actually does its advisible to read the Attachments Chapter in Developers Guide.

Primary Key information that uniquely identifies the product (such as the product_ID).

Below API takes single file from a shared drive and inserts into fnd_lobs. Then its been attached to a particular Id (like order_id, invoice_id..) using the combination of pk1_value, entity name and category. In this example i have used PDF as attachment. Quering the table fnd_document_datatypes will give different attachment types.

This sample shows how an attachment API works. You can use it to customize/enhance to requirement.

DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE:=<Primary Key information that uniquely identifies the product (such as the product_ID)>;
l_description fnd_documents_tl.description%TYPE:='Test Attachment';
l_filename VARCHAR2(240) := '<File Name>';
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length INTEGER;
l_entity_name VARCHAR2(100) := <entity_name>;
l_category_name VARCHAR2(100) := <category_name>;

BEGIN

fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;


-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name = <user_name>;

-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE';

-- Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;

-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
--apps.fnd_documents_long_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id
--l_media_id
FROM DUAL;


SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;

fils := BFILENAME (<FLIE PATH>, l_filename);

-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf
SYSDATE,
NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,
'US', 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;

-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;


-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id => l_short_datatype_id, -- FILE
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'N', --This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => 'S',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);

commit;



fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_language => 'US',
x_description => l_filename--l_description
);
COMMIT;
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
END;
/
FND_ATTACHED_DOCUMENTS:
FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity.  For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag to indicate whether or not an attachment was created automatically.
FND_DOCUMENTS_TL:
FND_DOCUMENTS_TL stores translated information about the documents in FND_DOCUMENTS. Each row includes the document identifier, the language the row is translated to, the description of the document, the file in which the image is stored, and an identifier (MEDIA_ID) of the sub-table in which the document is saved (FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).
FND_DOCUMENT_DATATYPES:
FND_DOCUMENT_DATATYPES stores the document datatypes that are supported. Initial values are: short text, long text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add datatypes to handle documents stored outside of Oracle and use non-native Forms applications to view/edit their documents. The table uses a “duplicate record” model for handling multi-lingual needs. That is, for each category there will be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.
FND_DOCUMENT_CATEGORIES:
FND_DOCUMENT_CATEGORIES stores information about the categories in which documents are classified. For example, documents may be considered “Bill of Material Comments”, “WIP Job Comments”, etc. Document categories are used to provide a measure of security on documents. Each form that enables the attachment feature lists which categories of documents can be viewed in the form. This table uses a “duplicate record” model for handling multi-lingual needs.
FND_DOCUMENTS_LONG_TEXT:
FND_DOCUMENTS_LONG_TEXT stores information about long text documents.
FND_DOCUMENTS_SHORT_TEXT:
FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.
FND_DOCUMENTS_LONG_RAW:
FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.
FND_DOCUMENT_ENTITIES:
FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, etc. Since the table uses a “duplicate record” model for handling multi-lingual needs, for each document entity there will be one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each language.

------------------------------------------------------------------------------------------------------------
To get long text of attachment at customer level
-------------------------------------
SELECT FDLT.LONG_TEXT    into v_useritemtext  
FROM  FND_DOCUMENTS_TL FDL,
      FND_DOCUMENTS_LONG_TEXT FDLT,
      fnd_attached_documents fad
WHERE  FDL.MEDIA_ID=FDLT.MEDIA_ID
and   fad.document_id=fdl.document_id
and   fad.entity_name = 'AR_CUSTOMERS'
AND   LANGUAGE      ='US'
AND   upper(FDL.DESCRIPTION) =upper('XXDESC')
and  fad.pk1_value =:BILL_TO_CUSTOMER_ID;