1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

sale order attachments API

Discussion in 'Oracle SCM & Manufacturing' started by Bharat, Apr 12, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi,

    I have attached document in sale order at line level through front-end application. But now my requirement is I want to attach the document for particular sale order at header level through API. If such API exists to update sale order with attachments please guide me to do.
     
  2. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Check this..Insert records into following tables you should be able to import attahments

    p_status := 'S';
    p_message := 'Attachment created successfully';
    IF p_operation_code = 'INSERT' THEN
    BEGIN
    SELECT fnd_documents_short_text_s.NEXTVAL
    INTO v_media_id
    FROM dual;
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'Unable to get short text next value';
    RAISE e_stop_process;
    END;
    BEGIN
    SELECT fnd_documents_s.NEXTVAL
    INTO v_doc_id
    FROM dual;
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'Unable to get fnd documents next value';
    RAISE e_stop_process;
    END;
    BEGIN
    SELECT datatype_id
    INTO v_data_type_id
    FROM fnd_document_datatypes
    WHERE name = 'SHORT_TEXT';
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While finding datatype id '||SQLERRM;
    RAISE e_stop_process;
    END;
    BEGIN
    SELECT category_id
    INTO v_cat_id
    FROM fnd_document_categories_tl
    WHERE user_name = p_cat_name;
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While finding category id '||SQLERRM;
    RAISE e_stop_process;
    END;
    BEGIN
    INSERT INTO fnd_documents
    (document_id,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    datatype_id,
    category_id,
    security_type,
    publish_flag ,
    usage_type,
    start_date_active)
    VALUES
    (v_doc_id,
    p_creation_date,
    v_user_id,
    p_last_update_date,
    v_user_id,
    v_data_type_id,
    v_cat_id,
    4,
    'Y',
    'O',
    SYSDATE);
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While inserting data into fnd documents '||SQLERRM;
    RAISE e_stop_process;
    END;
    BEGIN
    INSERT INTO fnd_documents_tl
    (document_id,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    LANGUAGE,
    description,
    media_id,
    source_lang)
    VALUES
    (v_doc_id,
    p_creation_date,
    v_user_id,
    p_last_update_date,
    v_user_id,
    'US',
    p_description,
    v_media_id,
    'US');
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While inserting data into fnd documents_tl '||SQLERRM;
    RAISE e_stop_process;
    END;
    BEGIN
    SELECT fnd_attached_documents_s.NEXTVAL
    INTO v_att_doc_id
    FROM dual;
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'Unable to get fnd attached documents next value';
    RAISE e_stop_process;
    END;
    BEGIN
    INSERT INTO fnd_attached_documents
    (attached_document_id,
    document_id,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    seq_num,
    entity_name,
    pk1_value,
    automatically_added_flag)
    VALUES
    (v_att_doc_id,
    v_doc_id,
    p_creation_date,
    v_user_id,
    p_last_update_date,
    v_user_id,
    p_seq_num,
    p_entity_name,
    p_pk_value,
    'N');
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While inserting data into fnd attached documents '||SQLERRM;
    RAISE e_stop_process;
    END;
    -- Fnd_File.put_line(Fnd_File.LOG,'The Values '||v_media_id||','||p_note);
    BEGIN
    INSERT INTO fnd_documents_short_text
    VALUES (v_media_id,
    p_note,
    NULL);
    EXCEPTION
    WHEN OTHERS THEN
    p_message := 'While inserting data into fnd_documents_short_text '||SQLERRM;
    RAISE e_stop_process;
    END;
    ELSIF p_operation_code = 'UPDATE' THEN
    BEGIN
    UPDATE fnd_documents_short_text
    SET short_text = p_note
    WHERE media_id IN (SELECT media_id
    FROM fnd_attached_documents fad,
    fnd_documents fd,
    fnd_documents_tl fdt,
    fnd_document_categories_tl fdct
    WHERE fad.document_id = fd.document_id
    AND fd.document_id = fdt.document_id
    AND fd.category_id = fdct.category_id
    AND fad.entity_name = p_entity_name
    AND fad.pk1_value = p_pk_value
    AND fdct.user_name = p_cat_name);
    EXCEPTION
    WHEN OTHERS THEN
    p_status := 'E';
    p_message := 'SQL Error while updating note :'||SQLERRM;
    END;
    END IF;
     
    Sadik likes this.
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thanks ramji, it's very useful and got successfully created API to attach file in sale order header level and line level.