, ,

API – Update Contact details of Contact Person(Suppliers)

declare
 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
 l_email_rec         HZ_CONTACT_POINT_V2PUB.email_rec_type;
 l_contact_point_id  NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
begin
    l_contact_point_rec.owner_table_id := 226285;                
    l_contact_point_rec.contact_point_type :=’EMAIL’;
    l_contact_point_rec.owner_table_name := ‘HZ_PARTIES’;
    l_contact_point_rec.created_by_module :=’POS_SUPPLIER_MGMT’;
    l_email_rec.email_address := ‘[email protected]‘ ;
 
HZ_CONTACT_POINT_V2PUB.create_email_contact_point
   (
    p_contact_point_rec =>  l_contact_point_rec,  
    p_email_rec         =>  l_email_rec, 
    x_contact_point_id  =>  l_contact_point_id,        
    x_return_status     =>  x_return_status,
    x_msg_count         =>  x_msg_count,
    x_msg_data          =>  x_msg_data
  );
  if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier contact !!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);
                            IF x_msg_count > 1 THEN
                                FOR i IN 1..x_msg_count LOOP
                                        dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                                END LOOP;
                            END IF;
            ELSE
                        dbms_output.put_line(‘Supplier Contact updated !!!!!!’||l_contact_point_id);

            end if; 
end;

/by
, ,

Setting Org Context in Oracle Apps

Setting the Multi Org Context :


METHOD 1:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT(ACCESS_MODE,ORG_ID);
end;

Example:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,101);
end;

S – Denotes that the current session will work for Single Org_id (101)

M – Denotes that the current session will work for Multiple Org_id


 
METHOD 2:

begin
mo_global.init (<APPLICATION SHORT NAME>);
end;

Example :

begin
mo_global.init (‘AR’);
end;


Query :
select Application_short_name , application_name
from   fnd_application fapp,
       fnd_application_tl fappt
where  fapp.APPLICATION_ID = fappt.application_id
and    fappt.language = ‘US’
and    application_name = ‘General Ledger’
—————————————————————–

Setting the Application Context :

METHOD 1:

begin
fnd_global.apps_initialize(p_user_id, p_resp_id, p_resp_appl_id);
end;

Example :

begin
      fnd_global.APPS_INITIALIZE(200131258,20004,51710);
end;

begin
      fnd_global.APPS_INITIALIZE
      (user_id       => 200131258,
       resp_id       => 20004,
       resp_appl_id  => 51710
      );
end;

Query to find resp_is , resp_appl_id and user_id

select  responsibility_id
       ,application_id
       ,responsibility_name
from   fnd_responsibility_tl
where  upper(responsibility_name) IN ( upper(‘Receivables Manager’), upper(‘Application Developer’ ) )
and    language = ‘US’;

select  user_id
from    fnd_user
where  upper(user_name) = ‘SAIF’;

SELECT fnd_profile.value (‘RESP_ID’) FROM dual

SELECT fnd_profile.value (‘USER_ID’) FROM dual

SELECT fnd_profile.value (‘APPLICATION_ID’) FROM dual

SELECT TO_NUMBER (FND_PROFILE.VALUE( ‘LOGIN_ID ‘)) FROM dual

SELECT FND_PROFILE.VALUE(‘ORG_ID’) FROM dual

SELECT FND_PROFILE.VALUE(‘SO_ORGANIZATION_ID’) FROM dual

SELECT FND_PROFILE.VALUE(‘USERNAME’) FROM dual

SELECT FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’) FROM dual

METHOD 2 :

begin
dbms_application_info.set_client_info(‘<org id>’);
end;

Example

begin
dbms_application_info.set_client_info(‘101’);
end;

/by
, ,

API – AR Invoice (Transaction) Creation

CREATE OR REPLACE procedure APPS.xx_ar_invoice_api
is
        l_return_status         varchar2(1);
        l_msg_count             number;
        l_msg_data              varchar2(2000);
        l_batch_source_rec      ar_invoice_api_pub.batch_source_rec_type;
        l_trx_header_tbl        ar_invoice_api_pub.trx_header_tbl_type;
        l_trx_lines_tbl         ar_invoice_api_pub.trx_line_tbl_type;
        l_trx_dist_tbl          ar_invoice_api_pub.trx_dist_tbl_type;
        l_trx_salescredits_tbl  ar_invoice_api_pub.trx_salescredits_tbl_type;
        l_cust_trx_id           number;

BEGIN
        
        begin
            MO_GLOBAL.SET_POLICY_CONTEXT('S',82);
        end;
        
        fnd_global.apps_initialize(1090,20678,222);

        l_batch_source_rec.batch_source_id          :=  1001;
        l_trx_header_tbl(1).trx_header_id           :=  9898;
        l_trx_header_tbl(1).trx_date                :=  sysdate;
        l_trx_header_tbl(1).trx_currency            :=  'AED';
        l_trx_header_tbl(1).cust_trx_type_id        :=  1000;
        l_trx_header_tbl(1).bill_to_customer_id     :=  1139;
        l_trx_header_tbl(1).term_id                 :=  1000;
        l_trx_header_tbl(1).finance_charges         :=  'N';
        l_trx_header_tbl(1).status_trx              :=  'OP';
        l_trx_header_tbl(1).printing_option         :=  'NOT';
        --l_trx_header_tbl(1).reference_number            :=  '1111';
        l_trx_lines_tbl(1).trx_header_id            :=  9898;
        l_trx_lines_tbl(1).trx_line_id              :=  101;
        l_trx_lines_tbl(1).line_number              :=  1;
        l_trx_lines_tbl(1).inventory_item_id        :=  1185;
       -- l_trx_lines_tbl(1).description              :=  'CAST IRON GRILL-325*485MM';
        l_trx_lines_tbl(1).quantity_invoiced        :=  3;
        l_trx_lines_tbl(1).unit_selling_price       :=  525;          --Price
        l_trx_lines_tbl(1).uom_code                 :=  'EAC';
        l_trx_lines_tbl(1).line_type                :=  'LINE';
        l_trx_dist_tbl(1).trx_dist_id               :=  101;
        l_trx_dist_tbl(1).trx_line_id               :=  101;
        l_trx_dist_tbl(1).ACCOUNT_CLASS             := 'REV';
        l_trx_dist_tbl(1).percent                   := 100;
        l_trx_dist_tbl(1).CODE_COMBINATION_ID       := 1012;
        
--Here we call the API to create Invoice with the stored values


    AR_INVOICE_API_PUB.create_invoice
    (p_api_version          => 1.0
    --,p_commit               => 'T'
    ,p_batch_source_rec     => l_batch_source_rec
    ,p_trx_header_tbl       => l_trx_header_tbl
    ,p_trx_lines_tbl        => l_trx_lines_tbl
    ,p_trx_dist_tbl         => l_trx_dist_tbl
    ,p_trx_salescredits_tbl => l_trx_salescredits_tbl
    ,x_return_status        => l_return_status
    ,x_msg_count            => l_msg_count
    ,x_msg_data             => l_msg_data
    );
    
    dbms_output.put_line('Created:'||l_msg_data||l_return_status);

    IF l_return_status = fnd_api.g_ret_sts_error OR
       l_return_status = fnd_api.g_ret_sts_unexp_error THEN

        dbms_output.put_line(l_return_status||':'||sqlerrm);
    Else 
        dbms_output.put_line(l_return_status||':'||sqlerrm);
        If (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL) Then
            Dbms_output.put_line('Invoice(s) suceessfully created!') ;
            Dbms_output.put_line('Batch ID: ' || ar_invoice_api_pub.g_api_outputs.batch_id);
            Dbms_output.put_line('customer_trx_id: ' || l_cust_trx_id);
        Else
            Dbms_output.put_line(sqlerrm);
        End If;
    end if;
    commit;
End;
/
/by
, ,

API – Stock Locator Creation

INVENTORY MANAGER > SETUP > ORGANIZATIONS > STOCK LOCATORS


CREATE TABLE XX_STOCK_LOCATOR_STAGING (LOCATOR_CONCAT_SEGMENTS VARCHAR2(2000))

-----------------------------------------------------------------------------------
create or replace procedure XX_CREATE_STOCK_LOCATORS
is
l_msg_data              VARCHAR2(100);
l_msg_count             NUMBER;
l_return_status         VARCHAR2(1);
l_locator_id            NUMBER;
l_locator_exists        VARCHAR2(1);
l_org_id                NUMBER := 1350; /*Organization_id */
l_organization_code     VARCHAR2(10) := '201'; /*Organization_Code */
l_sub_code              VARCHAR2(10) ; /*Variable for Subinventory*/
l_concatenated_segments VARCHAR2(100); /*Variable for Locator Segment*/

--l_user_id NUMBER := 1262; /* User ID From FND_users Table */
--l_resp_id NUMBER := 20634; /*Responsibility Id*/
--l_resp_appl_id NUMBER := 401; /* Responsibility Application id */

CURSOR C1
IS
SELECT * FROM XX_STOCK_LOCATOR_STAGING;

BEGIN
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/

FND_GLOBAL.APPS_INITIALIZE(1090,51007,401);

FND_MSG_PUB.INITIALIZE;

  FOR I IN C1 LOOP


    l_concatenated_segments := i.LOCATOR_CONCAT_SEGMENTS;

    l_sub_code := 'Main Store';

    l_org_id:= 1350;

    INV_LOC_WMS_PUB.CREATE_LOCATOR(
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            x_inventory_location_id     => l_locator_id,
            x_locator_exists            => l_locator_exists,
            p_organization_id           => l_org_id,
            p_organization_code         => l_organization_code,
            p_concatenated_segments     => l_concatenated_segments,
            p_description               => 'DEFAULT', /*You can also use here description of Your Locator Combination*/
            p_inventory_location_type   => 3,         -- Storage locator
            p_picking_order             => NULL,
            p_location_maximum_units    => NULL,
            p_subinventory_code         => l_sub_code, /*Subinventory Code */
            p_location_weight_uom_code  => NULL,
            p_max_weight                => NULL,
            p_volume_uom_code           => NULL,
            p_max_cubic_area            => NULL,
            p_x_coordinate              => NULL,
            p_y_coordinate              => NULL,
            p_z_coordinate              => NULL,
            p_physical_location_id      => NULL,
            p_pick_uom_code             => NULL,
            p_dimension_uom_code        => NULL,
            p_length                    => NULL,
            p_width                     => NULL,
            p_height                    => NULL,
            p_status_id                 => 1, -- Default status 'Active'
            p_dropping_order            => NULL
            );
            
            
       DBMS_OUTPUT.PUT_LINE('Return Status '||l_concatenated_segments||' - '||l_return_status);
       
                   /*
            IF l_return_status IN ('E', 'U') THEN
            DBMS_OUTPUT.PUT_LINE('# of Errors '||l_msg_count);

            IF l_msg_count = 1 THEN
            DBMS_OUTPUT.PUT_LINE('Error '||l_msg_data);
            ELSE
            FOR i IN 1..l_msg_count LOOP
            DBMS_OUTPUT.PUT_LINE('Error '||FND_MSG_PUB.GET(i, 'F'));
            END LOOP;
            END IF;
            ELSE
            DBMS_OUTPUT.PUT_LINE('Locator Id is '||l_locator_id);
            END IF;
            */
   END LOOP;
/by
, ,

Custom.pll in Oracle Application

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.
CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-
1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu
Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this?
Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?
 Once you make changes you need to compile the pll. Use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.
For following Events call will go to CUSTOM Library:
 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)
Custom Library contains Custom Package which is having two Functions and one procedure.
1] ZOOM_AVAILABLE:
This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is
form_name  varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘DEMXXEOR’ and block_name = ‘ORDERS’) then
return TRUE;
else
return FALSE;
end if;
end zoom_available;

Sample code2:

function zoom_available return Boolean is
form_name  varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘APXINWKB’ and block_name = ‘INV_SUM_FOLDER’)
then
return TRUE;
elsif (form_name = ‘APXINWKB’ and block_name = ‘LINE_SUM_FOLDER’)
then
return TRUE;
else
return FALSE;
end if;
end zoom_available;

2] STYLE:
This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard
By default it returns custom.standard.

Sample code:
 
function custom.style(event_name varchar2) return integer is
begin
if event_name = ’MY_CUSTOM_EVENT’ then
return custom.override;
else
return custom.standard;
end if;
end style;

3] EVENT: 
This procedure allows you to execute your code at specific events including:
  –    ZOOM
  –    WHEN-NEW-FORM-INSTANCE
  –    WHEN-NEW-BLOCK-INSTANCE
  –    WHEN-NEW-RECORD-INSTANCE
  –    WHEN-NEW-ITEM-INSTANCE
  –    WHEN-VALIDATE-RECORD
By default this routine must perform ‘null;’

Sample code:
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);
begin
if (form_name = ‘XXBI’ and block_name = ‘xxcc’) Then
if(event_name = ‘WHEN-NEW-FORM-INSTNACE’)
–Write your code here
elsif(event_name = ‘WHEN-VALIDATE-RECORD’)THEN
–Write your code here
else
null
end if;
end if;
end;

How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.
Forms Personalization: an alternative of custom.pll
In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.
Advantages of Forms Personalization over Custom.PLL:

  • Forms personalization can be used by an user with limited PL/SQL knowledge. 
  • Changes take place immediately on reopening the form.
  • Anything which can be done using Custom.PLL can be done using Forms Personalization also.
  • Personalizations are stored in base tables related to Form Personalization.
  • CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
  • Easy to disable/enable with click of a button.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.
/by