, , ,

How to Insert Attachments in R12 Using API

This article explains how to insert attachments using Oracle API’s from Back end. Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments) in Oracle Apps. To understand the below API and what it actually does its advisable to read the Attachments Chapter in Developers Guide.

https://docs.oracle.com/cd/E26401_01/doc.122/e22961/T302934T462356.htm

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

Below API takes single file from a shared drive and inserts into fnd_lobs. Then it’s attached to a particular Id (like check_id, invoice_id..) using the combination of pk1_value, entity name and category.

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

//Tested in R12.1.3

–Create a Server Directories in Oracle
–This will be done by DBA mostly
–Physical directory must be created inorder to save the files into server
CREATE OR REPLACE DIRECTORY SALE_INVOICE_PATH AS ‘/files/SALE_INVOICE/’;

SET SERVEROUTPUT ON;
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:= 999999; –Primary Key information that uniquely identifies the information
l_description fnd_documents_tl.description%type:= ‘Customer Invoice Attachment’;
l_filename VARCHAR2(240) := ‘Sales_Invoice_14Nov2016.pdf’;
l_file_path varchar2(240) := ‘SALE_INVOICE_PATH’; –Server Directory Path for upload files
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) := ‘CUSTOM_LOAD’; –Must be defined before or use existing ones. Table: FND_DOCUMENT_ENTITIES
l_category_name VARCHAR2(100) := ‘Other’; –Must be defined before or use existing ones.

BEGIN
–Enter USER_ID,RESP_ID,RESP_APPL_ID
fnd_global.apps_initialize (&USER_ID, &RESP_ID, &RESP_APPL_ID);

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 = ‘OPERATIONS’; –Username who will be uploading file.

— 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
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
into l_document_id,l_attached_document_id
FROM DUAL;

SELECT fnd_lobs_s.nextval
INTO l_media_id
FROM dual;

fils := BFILENAME (l_file_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’,
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,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE(‘LOGIN_ID’),
x_datatype_id => l_short_datatype_id,
X_security_id => 21, –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_description,
x_file_name => l_filename,
x_media_id => l_media_id
);

commit;

— Description informations will be stored in below table based on languages.
fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE(‘LOGIN_ID’),
x_language => ‘US’,
x_description => 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,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_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 => 21, –Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => ‘Y’,
x_language => ‘US’,
x_description => 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;
/

Learn more on Attachments on this article.

/by
, , , ,

R12 Supplier Contact creation API

Below are the steps to create Supplier contacts in Oracle EBS R12.
AP_SUP_SITE_CONTACT_INT interface used in 11i for loading supplier contact. Currently in R12 contacts can not be loaded using the interface table.

ap_vendor_pub_pkg.create_vendor_contact API is used in R12 and the program has to be registered as a concurrent program.

### Sample R12 Code

BEGIN
v_party_usg_assignment_id := NULL;
v_relationship_id := NULL;
v_directional_flag := NULL;
v_rel_rowid := NULL;
fnd_file.put_line (fnd_file.LOG,
‘Vendor code :’
|| rec.vendor_name
|| ‘  Vendor site code :’
|| rec.vendor_site_code
|| ‘  Person Last Name : ‘
|| rec.person_last_name
);
l_vendor_contact.vendor_id := rec.vendor_id;
l_vendor_contact.vendor_site_id := rec.vendor_site_id;
l_vendor_contact.org_id := rec.org_id;
l_vendor_contact.person_first_name := rec.person_first_name;
l_vendor_contact.person_middle_name := rec.person_middle_name;
l_vendor_contact.person_last_name := rec.person_last_name;
l_vendor_contact.phone := rec.phone;
l_vendor_contact.email_address := rec.email_address;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;

IF rec.process_flag = ‘I’
THEN
fnd_file.put_line (fnd_file.LOG, ‘Creating contacts….’);
ap_vendor_pub_pkg.create_vendor_contact
(p_api_version             => p_api_version,
p_init_msg_list           => p_init_msg_list,
p_commit                  => p_commit,
x_return_status           => x_return_status,
x_msg_count               => x_msg_count,
x_msg_data                => x_msg_data,
p_vendor_contact_rec      => l_vendor_contact,
x_vendor_contact_id       => l_vendor_contact.vendor_contact_id,
x_per_party_id            => l_vendor_contact.per_party_id,
x_rel_party_id            => l_vendor_contact.relationship_id,
x_rel_id                  => l_vendor_contact.rel_party_id,
x_org_contact_id          => l_vendor_contact.org_contact_id,
x_party_site_id           => l_vendor_contact.party_site_id
);
error_handling (rec.r_id, x_return_status, x_msg_count, x_msg_data);
fnd_file.put_line (fnd_file.LOG, ‘*************’);
END IF;
END;

/by
, , ,

How to Get GL Code Combination Description

Script 1:(Works in Both 11i & R12)

SELECT gcc.segment1
||’.’
||gcc.segment2
||’.’
||gcc.segment3 ACCOUNT,
A1.DESCRIPTION
||’-‘
||A2.DESCRIPTION
||’-‘
|| A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
gl_code_combinations gcc
WHERE a1.flex_value       =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT1′
)
AND a2.flex_value         =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT2′
)
AND a3.flex_value         =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT3′

);

Script 2: ( Applicable in R12)

SELECT gcc.CONCATENATED_SEGMENTS,
gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description

FROM gl_code_combinations_kfv gcc;

/by
, ,

R12 – Register, Attach, Submit, Delete Concurrent program using API

The scripts in this blog can be used to:
1)    Register the executable and Program
2)    Attach Concurrent program to a Request Group
3)    Submit Concurrent program

1)    Registering the Executable from back end
          Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too.
          Below is the PL/SQL code to create an executable from back-end.
         BEGIN
              FND_PROGRAM.executable(‘XXMZ_EMPLOYEE’ — executable
                                                              , ‘XXMZ Custom’ — application
                                                              , ‘XXMZ_EMPLOYEE’ — short_name
                                                              , ‘Executable for Employee INFORMATION’ — description
                                                              , ‘PL/SQL Stored Procedure’ — execution_method
                                                              , ‘XXMZ_EMPLOYEE’ — execution_file_name
                                                              , ” — subroutine_name
                                                              , ” — Execution File Path
                                                              , ‘US’ — language_code
                                                              , ”);
             COMMIT;
         END;
       Query in the front-end to see whether your executable is created.

2)    Registering the Concurrent program from back end
            Usually we create Concurrent program in the front-end, but this can be done from the database tier too.
            Below is the program to create a Concurrent program from back-end.
            BEGIN
                     FND_PROGRAM.register(‘Concurrent program for Employee Information’ — program
                                                                , ‘XXMZ Custom’ — application
                                                                , ‘Y’ — enabled
                                                                , ‘XXMZ_EMPLOYEE’ — short_name
                                                                , ‘ Employee Information’ — description
                                                                , ‘XXMZ_EMPLOYEE’ — executable_short_name
                                                                , ‘XXMZ Custom’ — executable_application
                                                                , ” — execution_options
                                                                , ” — priority
                                                                , ‘Y’ — save_output
                                                                , ‘Y’ — print
                                                                , ” — cols
                                                                , ” — rows
                                                                , ” — style
                                                                , ‘N’ — style_required
                                                                , ” — printer
                                                                , ” — request_type
                                                                , ” — request_type_application
                                                                , ‘Y’ — use_in_srs
                                                                , ‘N’ — allow_disabled_values
                                                                , ‘N’ — run_alone
                                                                , ‘TEXT’ – output_type
                                                                , ‘N’ — enable_trace
                                                                , ‘Y’ — restart
                                                                , ‘Y’ — nls_compliant
                                                                , ” — icon_name
                                                                , ‘US’); — language_code
                        COMMIT;
            END;

 Query in the front-end to see whether your Concurrent program is created .

3)    Attaching the concurrent program to the request group
               Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too.
               Below is the program to Attach Concurrent program to the request group from back-end.
               BEGIN
                        FND_PROGRAM.add_to_group(‘XXMZ_EMPLOYEE’ — program_short_name
                                                                             , ‘XXMZ Custom’ — application
                                                                             , ‘xxmz Request Group’ — Report Group Name
                                                                             , ‘XXMZ’); — Report Group Application
                        COMMIT;
              END;

  Query in the front-end to see whether your Concurrent program is Attached to Request Group.

4)    Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using
                        fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id)
and then run fnd_request.submit_request
                         DECLARE
                               l_request_id NUMBER(30);
                         begin
                                  FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
                                  l_request_id:= FND_REQUEST.SUBMIT_REQUEST (‘XXMZ’ –Application Short name,
‘VENDOR_FORM’– Concurrent Program Short Name );
                                  DBMS_OUTPUT.PUT_LINE(l_request_id);
                                  commit;
                         end;

Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.

SELECT * FROM FND_CONCURRENT_REQUESTS WHERE   REQUEST_ID= l_request_id;

You can use following code to wait for the request. It will return Boolean value.

FND_CONCURRENT.WAIT_FOR_REQUEST
                                   (request_id IN number default NULL,
                                    interval IN number default 60,
                                    max_wait IN number default 0,
                                    phase OUT varchar2,
                                    status OUT varchar2,
                                    dev_phase OUT varchar2,
                                    dev_status OUT varchar2,
                                    message OUT varchar2);

5) Delete a concurrent program from back-end

BEGIN   apps.fnd_program.remove_from_group (      program_short_name    => ‘CTAR_AR_TRX_LINE_CONVERSION’,      program_application   => ‘CTAR’,      request_group         => ‘Receivables All’,      group_application     => ‘Receivables’);   fnd_program.delete_program (‘CTAR_AR_ACCTD_AMT_DUE_RMNG’, ‘CTAR’);   fnd_program.delete_executable (‘CTAR_AR_ACCTD_AMT_DUE_RMNG’, ‘CTAR’);   COMMIT;END;

/by
, , , ,

R12 – Ship Confirm using API

Use this script to create a procedure in Database and call the procedure by passing the delivery number as a parameter to ship confirm it.
You can set the options for
1. Back ordering unspecified quantities
2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successful
SHIP CONFIRMATION THROUGH API

CREATE OR REPLACE PROCEDURE erps_ship_confirm_delivery (
   v_delivery_name      IN     VARCHAR2,                   —  delivery number
   v_action             IN     VARCHAR2, — Pass ‘B’ to backorder the unspecified quantity
   p_ship_conf_status      OUT VARCHAR2,
   x_msg_data              OUT VARCHAR2)
IS
   p_api_version_number     NUMBER;

   init_msg_list            VARCHAR2 (30);

   x_msg_count              NUMBER;

   x_msg_details            VARCHAR2 (32000);

   x_msg_summary            VARCHAR2 (32000);

   p_validation_level       NUMBER;

   p_commit                 VARCHAR2 (30);

   x_return_status          VARCHAR2 (15);

   source_code              VARCHAR2 (15);

   changed_attributes       wsh_delivery_details_pub.changedattributetabtype;

   p_action_code            VARCHAR2 (15);

   p_delivery_id            NUMBER;

   p_delivery_name          VARCHAR2 (30);

   p_asg_trip_id            NUMBER;

   p_asg_trip_name          VARCHAR2 (30);

   p_asg_pickup_stop_id     NUMBER;

   p_asg_pickup_loc_id      NUMBER;

   p_asg_pickup_loc_code    VARCHAR2 (30);

   p_asg_pickup_arr_date    DATE;

   p_asg_pickup_dep_date    DATE;

   p_asg_dropoff_stop_id    NUMBER;

   p_asg_dropoff_loc_id     NUMBER;

   p_asg_dropoff_loc_code   VARCHAR2 (30);

   p_asg_dropoff_arr_date   DATE;

   p_asg_dropoff_dep_date   DATE;

   p_sc_action_flag         VARCHAR2 (10);

   p_sc_close_trip_flag     VARCHAR2 (10);

   p_defer_iface            VARCHAR2 (10);

   p_sc_create_bol_flag     VARCHAR2 (10);

   p_sc_stage_del_flag      VARCHAR2 (10);

   p_sc_trip_ship_method    VARCHAR2 (30);

   p_sc_actual_dep_date     VARCHAR2 (30);

   p_sc_report_set_id       NUMBER;

   p_sc_report_set_name     VARCHAR2 (60);

   p_wv_override_flag       VARCHAR2 (10);

   x_trip_id                VARCHAR2 (30);

   x_trip_name              VARCHAR2 (30);

   p_msg_data               VARCHAR2 (32000);

   fail_api                 EXCEPTION;
BEGIN
   x_return_status := wsh_util_core.g_ret_sts_success;

   p_action_code := ‘CONFIRM’;

   p_delivery_name := v_delivery_name;

   p_sc_action_flag := v_action;

   p_sc_close_trip_flag := ‘Y’; — Trip stop concurrent program will be submitted automatically

   p_defer_iface := ‘N’;

   wsh_deliveries_pub.
    delivery_action (p_api_version_number        => 1.0,
                     p_init_msg_list             => init_msg_list,
                     x_return_status             => x_return_status,
                     x_msg_count                 => x_msg_count,
                     x_msg_data                  => p_msg_data,
                     p_action_code               => p_action_code,
                     p_delivery_id               => p_delivery_id,
                     p_delivery_name             => p_delivery_name,
                     p_asg_trip_id               => p_asg_trip_id,
                     p_asg_trip_name             => p_asg_trip_name,
                     p_asg_pickup_stop_id        => p_asg_pickup_stop_id,
                     p_asg_pickup_loc_id         => p_asg_pickup_loc_id,
                     p_asg_pickup_loc_code       => p_asg_pickup_loc_code,
                     p_asg_pickup_arr_date       => p_asg_pickup_arr_date,
                     p_asg_pickup_dep_date       => p_asg_pickup_dep_date,
                     p_asg_dropoff_stop_id       => p_asg_dropoff_stop_id,
                     p_asg_dropoff_loc_id        => p_asg_dropoff_loc_id,
                     p_asg_dropoff_loc_code      => p_asg_dropoff_loc_code,
                     p_asg_dropoff_arr_date      => p_asg_dropoff_arr_date,
                     p_asg_dropoff_dep_date      => p_asg_dropoff_dep_date,
                     p_sc_action_flag            => p_sc_action_flag,
                     p_sc_close_trip_flag        => p_sc_close_trip_flag,
                     p_sc_create_bol_flag        => p_sc_create_bol_flag,
                     p_sc_stage_del_flag         => p_sc_stage_del_flag,
                     p_sc_trip_ship_method       => p_sc_trip_ship_method,
                     p_sc_actual_dep_date        => p_sc_actual_dep_date,
                     p_sc_report_set_id          => p_sc_report_set_id,
                     p_sc_report_set_name        => p_sc_report_set_name,
                     p_sc_defer_interface_flag   => p_defer_iface,
                     p_wv_override_flag          => p_wv_override_flag,
                     x_trip_id                   => x_trip_id,
                     x_trip_name                 => x_trip_name);

   IF (x_return_status != wsh_util_core.g_ret_sts_success)
   THEN
      wsh_util_core.get_messages (‘Y’,
                                  x_msg_summary,
                                  x_msg_details,
                                  x_msg_count);

      IF x_msg_count > 1
      THEN
         x_msg_data := x_msg_summary || x_msg_details;
      ELSE
         x_msg_data := x_msg_summary;
      END IF;

      p_ship_conf_status := ‘E’;
   ELSE
      p_ship_conf_status := ‘S’;
   END IF;

END erps_ship_confirm_delivery;

SHIP CONFIRMATION THROUGH FORMS
Navigate to Shipping responsibility >> Shipping >> Transactions
Query the delivery that need to be ship confirmed
click ship confirm button.

/by