Thursday 27 December 2012

Oracle Application's URL from back end

option 1

SELECT home_url
   FROM icx_parameters

option 2


Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
      (SELECT PROFILE_OPTION_ID
       FROM FND_PROFILE_OPTIONS
       WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
       AND LEVEL_VALUE=0;

API to delete the program


API to delete the program

fnd_program.delete_program('Short Name', 'Application');

API to delete the executable

fnd_program.delete_executable('Short Name', 'Application');


Please commit after you are done.

Wednesday 26 December 2012

Creating Soft Link for Unix file in Oracle Apps

Go to the top where .prog file exists.

cd $AR_TOP/bin

ln -s ../../../../fnd/12.0.0/bin/fndcpesr  XX123

Compile Oracle Report/RDF in UNIX

rwconverter userid=apps/apps_password batch=yes source=file_name stype=rdffile DTYPE=rdfFILE compile_all=yes OVERWRITE=yes logfile=log.txt dest=outfile_name

Thursday 11 October 2012

Costing Creation/Allocation API in HRMS

DECLARE
l_combination_name VARCHAR2(100);
l_cost_allocation_id
NUMBER; l_effective_start_date
DATE; l_effective_end_date
DATE; l_cost_allocation_keyflex_id
NUMBER;l_cc_object_version_number NUMBER; BEGIN
pay_cost_allocation_api.create_cost_allocation
(p_validate => FALSE,
 p_effective_date => SYSDATE,
 p_assignment_id => 11972,
 p_proportion => 1,
 p_business_group_id => 81,
 p_segment1 => '600',
 p_segment2 => '310000',
 p_segment3 => 'AFA',
 p_combination_name => l_combination_name
 p_cost_allocation_id => l_cost_allocation_id
 p_effective_start_date => l_effective_start_date
 p_effective_end_date => l_effective_end_date
 p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
 p_object_version_number => l_object_version_number
);
EXCEPTIONWHEN OTHERSTHENapi_msg := SUBSTR(SQLERRM,1,2400);
Fnd_File.put_line(Fnd_File.log,'Error - '||api_msg);
END;

ORA-20001: PAY_50983_INVALID_PROPORTION:

PAY_COST_ALLOCATION_API failing with the error below when the parameter value for p_proportion is set to '100'?
ORA-20001: PAY_50983_INVALID_PROPORTION:
ORA-06512: at "APPS.PAY_COST_ALLOCATION_API", line 721
ORA-06512: at line 13

ANSWER:

The parameter value for p_proportion should be set to '1'.  This is the equivalent of 100% entered via the application screen.

Tuesday 9 October 2012

Query to Extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Wednesday 3 October 2012

Organizations in HRMS

Technical Details

Tables

HR_ALL_ORGANIZATION_UNITS
The basic details of an organization are stored here.
There is also a translation table HR_ALL_ORGANIZATION_UNITS_TL for the organization name in each language installed.

HR_ORG_INFORMATION_TYPES
This holds the different types of organization information.
Each type is defined in the Org Developer DF as a context value. eg Work Day Information, Business Group Information, etc.
When using the Organizations screen you would see the information types relevant to the org classification.

HR_ORG_INFO_TYPES_BY_CLASS
This table holds the org information types that are available for each classification.
This data is seeded and there is no screen that displays these groupings.
When you select a classification on the Org screen and press the Other button, this table is referenced to display the relevant org information types.

HR_ORGANIZATION_INFORMATION
This table is more complicated as it stores two distinct sets of information.
To know which type of information is stored you need to check the value in column ORG_INFORMATION_CONTEXT.
When the value is 'CLASS' the row is used to link an organization to a classification. There will be one row for every classification used by an organization. The classification name is held in column ORG_INFORMATION1.
If you create an organization, a row will be added here for every classification you save against that org.
When the value is set to an information type (from HR_ORG_INFORMATION_TYPES) the columns ORG_INFORMATION1-20 are used to hold the values for that information type.
So when you are in the Define Organizations screen and you press the others button and select an information type, the values you see in the fields on the screen come from this table.
The information type is a dff context with some segments defined. Each segment is mapped to one of the ORG_INFORMATIONx columns in this table. When you open the information type field you see the individual segments and the value for each segment is held in the column in table HR_ORGANIZATION_INFORMATION that matches the column specified in the segment definition.
The lookup ORG_TYPE holds values for the 'Type' field on the Define Organization screen.

The lookup ORG_CLASS holds values for the Classifications Name field on the Define Organization screen.


This sql will prompt you for an organization name

SQL> select i.organization_id, o.name, l.meaning
from hr_all_organization_units o
, hr_organization_information i
, fnd_lookup_values l
where o.organization_id = i.organization_id
and o.name = '&Organization_Name'
and i.org_information1 = l.lookup_code
and l.lookup_type = 'ORG_CLASS'
and i.org_information_context = 'CLASS';


This sql will prompt you for an organization information values

SQL> select o.name
, i.org_information1 Normal_Start_Time
, i.org_information2 Normal_End_Time
, i.org_information3 Working_Hours
, i.org_information4 Frequency
from hr_organization_information i
, hr_all_organization_units o
where o.name = '&Organization_Name'
and o.organization_id = i.organization_id
and i.org_information_context = 'Work Day Information';

Profile Option Values Query in Oracle Apps

SELECT distinct
fpot.profile_option_name profile_short_name
,substr(fpot.user_profile_option_name,1,60) profile_name
,DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
,fr.responsibility_name,DECODE(fpov.level_id,10001,
null,
10002,
fa.application_short_name
,10003,
fr.responsibility_name,
10004,
fu.user_name
) level_value,
fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl(fpo.profile_option_name,fpo.profile_option_name)
or fpot.user_profile_option_name like nvl(fpot.user_profile_option_name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fu.end_date is null

Profile Option Values Load using API in Oracle Apps

BEGIN

FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW
(X_ROWID=>X_ROWID,
 X_APPLICATION_ID=>v_application_id,
 X_PROFILE_OPTION_ID=>v_profile_option_id,
 X_LEVEL_ID=>crec.level_id,
 X_LEVEL_VALUE =>L_LEVEL_VALUE,
 X_CREATION_DATE =>SYSDATE,
 X_CREATED_BY =>fnd_global.login_id,
 X_LAST_UPDATE_DATE =>SYSDATE,
 X_LAST_UPDATED_BY =>fnd_global.login_id,
 X_LAST_UPDATE_LOGIN=>fnd_global.login_id,
 X_PROFILE_OPTION_VALUE=>crec.profile_value,
 X_LEVEL_VALUE_APPLICATION_ID=>crec.level_value_application_id,
 X_LEVEL_VALUE2=>NULL
); 
END;
---
---
L_LEVEL_VALUE based on the Profile level.

IF profile_level ='site'

THENL_LEVEL_VALUE :=NULL;ELSIF
profile_level ='Appl'
THENL_LEVEL_VALUE :=l_application_id(from fnd_application);
ELSIF
profile_level ='Resp'THENL_LEVEL_VALUE :=v_responsibility_id(from FND_RESPONSIBILITY);
ELSIF
profile_level ='User'THENL_LEVEL_VALUE :=v_user_id;(from FND_USERS);
END IF;

Download/Uplaod Value Sets in Oracle Apps

Value Sets

FNDLOAD apps/hrapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XX_FUNCTIONS_LOV'

Value Set Values

FNDLOAD apps/hrapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV_VAL.ldt VALUE_SET FLEX_VALUE_SET_NAME='XX_FUNCTIONS_LOV'

Uplaod

FNDLOAD apps/mig3apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV.ldt

Friday 28 September 2012

f60gen in R12

frmcmp_batch userid=apps/crpapps module=XXAPINVS.fmb output_file=XXAPINVS.fmx module_type=form batch=no compile_all=yes

HOW TO SET WHO COLUMNS TO FORM

open pre-insert ,
       pre-update,
       pre-delete triggers

using fnd_standard.set_who;

Creating Form Functions in Oracle APPS using API

DECLARE
l_new_menu_id     NUMBER; 
l_last_menu_id    NUMBER; 
l_row_id          VARCHAR2(100); 
l_descr           VARCHAR2(400); 
l_flag_exist_menu NUMBER := 0;
x_function_id     NUMBER; 
x_row_id          ROWID;
x_form_id         NUMBER;
lc_status         varchar2(1);
CURSOR C1
IS
SELECT * FROM
XX_FND_FUNCTIONS
where FUNCTION_NAME NOT in
(select FUNCTION_NAME from FND_FORM_FUNCTIONS);

 BEGIN

   FOR CREC IN C1
    LOOP   
        lc_status := 'Y';
       
        BEGIN
        select fnd_form_functions_s.nextval
          into x_function_id
          from sys.dual;
            Exception
                When others then
                dbms_output.put_line('failed to fnd_form_functions_s');
                lc_status := 'E';
            
          END;
      
       IF CREC.form_id IS NOT NULL THEN
   
        BEGIN
        SELECT  form_id
          into  x_form_id
          FROM  fnd_form
          where form_id=CREC.form_id;
           Exception
                When others then
                dbms_output.put_line('failed to get x_form_id');
                lc_status := 'E';          
          END;
       
       END IF;
       
         if lc_status = 'Y'then
        
         FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
                    X_ROWID => x_row_id,
                    X_FUNCTION_ID => X_FUNCTION_ID,
                    X_WEB_HOST_NAME => null,
                    X_WEB_AGENT_NAME => null,
                    X_WEB_HTML_CALL => CREC.WEB_HTML_CALL,
                    X_WEB_ENCRYPT_PARAMETERS =>'N',
                    X_WEB_SECURED =>'N',
                    X_WEB_ICON =>null,
                    X_OBJECT_ID =>null,
                    X_REGION_APPLICATION_ID =>null,
                    X_REGION_CODE =>null,
                    X_FUNCTION_NAME =>CREC.FUNCTION_NAME, -- find unique function name using query
                    X_APPLICATION_ID =>CREC.APPLICATION_ID, -- find application id using the query for appliaion names and pass 'FND'
                    X_FORM_ID => x_form_id, -- find from id using formname 'XXCO'
                    X_PARAMETERS => CREC.PARAMETERS,
                    X_TYPE => CREC.TYPE,
                    X_USER_FUNCTION_NAME =>CREC.USER_FUNCTION_NAME,
                    X_DESCRIPTION =>CREC.DESCRIPTION,
                    X_CREATION_DATE =>sysdate,
                    X_CREATED_BY => -1,
                    X_LAST_UPDATE_DATE =>sysdate,
                    X_LAST_UPDATED_BY =>-1,
                    X_LAST_UPDATE_LOGIN =>0
                    );
       
         dbms_output.put_line('FUNCTION_NAME '||CREC.FUNCTION_NAME);        
        END IF;   
   END LOOP;
  
   commit;       
       
 end;

Creating Menus in Oracle Apps Using API

DECLARE
l_new_menu_id     NUMBER; 
l_last_menu_id    NUMBER; 
l_row_id          VARCHAR2(100); 
l_descr           VARCHAR2(400); 
l_flag_exist_menu NUMBER := 0;
lc_status         VARCHAR2(1);
v_menu_id         NUMBER;
v_function_id     NUMBER;
v_sub_menu_id     NUMBER;
CURSOR C1
IS
SELECT A.*
FROM   XX_FND_MENU_ENTRIES A,
       FND_MENUS B
WHERE  A.MENU_NAME=B.MENU_NAME
       AND  (MENU_ID,ENTRY_SEQUENCE) NOT IN (SELECT MENU_ID,ENTRY_SEQUENCE FROM FND_MENU_ENTRIES_VL);
 BEGIN
 
  FOR CREC IN C1
  LOOP     
     lc_status := 'Y';
   
        -- get the menu_id
        --
       
            BEGIN
            select menu_id
            into v_menu_id
            from fnd_menus
            where menu_name = CREC.MENU_NAME;
            Exception       
                When others then
                dbms_output.put_line('Invalid Menu' ||CREC.MENU_NAME);
                lc_status := 'E';             
            END;
           
        -- get the menu_id
        --
           IF CREC.function_name IS NOT NULL
           THEN
       
            BEGIN
            select function_id
            into   v_function_id
            from   fnd_form_functions
            where  function_name = CREC.function_name;
            Exception       
                When others then
                dbms_output.put_line('Invalid function_name' || CREC.function_name);
                lc_status := 'E';             
            END;  
           
           END IF;
                     
            -- get the Sub menu_id
        --
              
        IF CREC.SUB_MENU_NAME IS NOT NULL  
         THEN  
         BEGIN
           select menu_id
            into v_sub_menu_id
            from fnd_menus
           where menu_name =CREC.SUB_MENU_NAME;
            Exception       
            When others then
           dbms_output.put_line('Invalid Sub Menu' || CREC.SUB_MENU_NAME);
           lc_status := 'E';             
            END;
           END IF;
          
     
      IF lc_status <> 'E' THEN      
      
     
         select nvl(max(1),0) into l_flag_exist_menu    
             from fnd_menu_entries t    
             where t.menu_id = v_menu_id       
             and ( (v_function_id is null and t.sub_menu_id is null)
             or  t.sub_menu_id = v_sub_menu_id)          
             and ( (v_function_id is null and t.function_id is null)
             or t.function_id = v_function_id)          
             and 1=1;
        dbms_output.put_line('l_flag_exist_menu   '||l_flag_exist_menu);  
        
             if (l_flag_exist_menu = 0) then
        
              FND_MENU_ENTRIES_PKG.INSERT_ROW
              (X_ROWID             => l_row_id,       
               X_MENU_ID           => v_menu_id,      
               X_ENTRY_SEQUENCE    => crec.entry_sequence,
               X_SUB_MENU_ID       => v_sub_menu_id,      
               X_FUNCTION_ID       => v_function_id,      
               X_GRANT_FLAG        => crec.grant_flag,       
               X_PROMPT            => crec.prompt,       
               X_DESCRIPTION       => crec.description,       
               X_CREATION_DATE     => sysdate,       
               X_CREATED_BY        => fnd_global.user_id,       
               X_LAST_UPDATE_DATE  => sysdate,       
               X_LAST_UPDATED_BY   => fnd_global.user_id,       
               X_LAST_UPDATE_LOGIN => fnd_global.login_id);          
       
                dbms_output.put_line('Prompt '||CREC.prompt);
             END IF;         
      
         END IF;
      
   END LOOP;
  
   commit;       
       
 end;

Oracle error -20002: ORA-20002: [WF_NO_ROLE]

Solution

a) Set the Enable Security Groups profile option to Yes at application level for the application relevant to the responsibility
b) Run the CREATE FND_RESP WF ROLES concurrent program from the System Administrator responsibility.

Wednesday 26 September 2012

API - Supplier & Supplier Site Creation


API - Supplier & Supplier Site Creation declare
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
l_upd number := 0;
x_vendor_id number;
x_party_id number;
x_vendor_site_id number;
x_party_site_id number;
x_location_id number;
l_party_site_id number;
l_organization_id number;
begin
--mo_global.set_policy_context('S',1650);
l_vendor_rec.SEGMENT1 := '123';
l_vendor_rec.VENDOR_NAME := 'TEST123';
l_vendor_rec.SUMMARY_FLAG := 'N';
l_vendor_rec.ENABLED_FLAG := 'Y';


AP_VENDOR_PUB_PKG.Create_Vendor
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id
);

if (x_return_status <> 'S') then
dbms_output.put_line('ERROR While supplier creation!!!');
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



l_vendor_site_rec.vendor_id := x_vendor_id;
l_vendor_site_rec.VENDOR_SITE_CODE := 'LONDON';
l_vendor_site_rec.org_id := 1650;
l_vendor_site_rec.COUNTRY := 'UK';
l_vendor_site_rec.ADDRESS_LINE1 :='London';
l_vendor_site_rec.PURCHASING_SITE_FLAG := 'Y';
l_vendor_site_rec.PAY_SITE_FLAG := 'Y';


AP_VENDOR_PUB_PKG.Create_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id
);

if (x_return_status <> 'S') then
dbms_output.put_line(' ERROR while supplier site creation!!!');
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 Site Created!!!');

end if;
end if;
end;

API - Vendor Site Update

API - Vendor Site Update l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_vendor_site_rec.vendor_id := v_vendor_id; -- Supplier Id
l_vendor_site_rec.org_id := v_org_id; -- Operating Unit id
x_vendor_site_id :=v_site_id; -- Site Id to be updated

l_vendor_site_rec.email_address := 'anilmca02@gmail.com'; -- eg: Email address update

AP_VENDOR_PUB_PKG.Update_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => x_vendor_site_id
);

Wednesday 19 September 2012

Enable Descriptive Flexfields [DFF] in a Custom Forms

Steps for creating DFF in Custom Form


Go to Application Developer
Flex Fields----------Descriptive Flex Fields
1) Select the application: Appropriate Top
2) DFF Name: You’r Choice
3) Title: You’r Choice
4) Description: You’r Choice
5) Table Name: The name which we are using in the Block (We need to create using




Table :EXECUTE ad_dd.register_table('XXTCC', 'XXTBK_OPM_INGRID_BATCH', 'T',8, 10, 90);

Columns
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE_CATEGORY1',19, 'VARCHAR2', 150, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE1',20, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE2',21, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE3',22, 'VARCHAR2', 50, 'N', 'N');

EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE4',23, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE5',24, 'VARCHAR2', 50, 'N', 'N');



And fill all the above details as below.













Click on Columns Select the list of columns to be displayed in DFF











Next
Segments:Query your DFF












Attach the Value set or leave it as blank

incase user will enter manually.










These are all the steps to be followed to create a DFF in Custom Form.
And next go to forms
The below are two mandatory steps to be followed in FMB1) Create Item ITEM_DFF for which property:
TEXT_ITEM_DESC_FLEX
2) And write the below in code in WHEN_NEW_ITEM_INSTANCE for
the item ITEM_DFF
fnd_flex.event ('WHEN-NEW-ITEM-INSTANCE');
3) Finally in WHEN-NEW_FORM_ INSTANCE we have to call the felx filed structure.

fnd_descr_flex.define ( block => Block name in which the DFF
field => Field from which DFF
appl_short_name => 'XXTCC',
desc_flex_name => TCC_PROD_BATCH_DFF'
);

Tuesday 18 September 2012

Oracle Receivables Deposits API

begin
mo_global.set_policy_context('S','85'); -- pass the org id value
end;
______________________________________________________________________
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_count NUMBER;
l_new_trx_number ra_customer_trx.trx_number%type;
l_new_customer_trx_id ra_customer_trx.customer_trx_id%type;
l_new_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
l_new_rowid VARCHAR2(240);
l_new_status VARCHAR2(240);
BEGIN
fnd_global.apps_initialize (user_id => 1438,resp_id =>50801,resp_appl_id => 222);
--Standard API parameters
ar_deposit_api_pub.CREATE_DEPOSIT(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_deposit_date => trunc(sysdate),
p_currency_code => 'GBP',
p_amount => 10000,
p_batch_source_id => 1141,
p_cust_trx_type_id => 1080,
p_class => 'DEP',
p_bill_to_customer_id => 27042,
p_start_date_commitment => sysdate,
p_description => 'Migration Testing',
p_remit_to_address_id => 1060,
p_commit => FND_API.G_TRUE,
X_new_trx_number =>l_new_trx_number,
X_new_customer_trx_id =>l_new_customer_trx_id,
X_new_customer_trx_line_id =>l_new_customer_trx_line_id,
X_new_rowid =>l_new_rowid,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ) ;
IF l_msg_count = 1 Then
-- There is one message raised by the API, so it has been sent out.
dbms_output.put_line('l_msg_data 'l_msg_data);
ELSIF l_msg_count > 1 Then
--The messages on the stack are more than one, so call them in a loop.
--And print the messages
LOOP
IF nvl(l_count,0) < l_msg_count THEN
l_count := nvl(l_count,0) +1 ;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_count = 1 THEN
dbms_output.put_line('l_msg_data 1 'l_msg_data);
ELSIF l_count = 2 THEN
dbms_output.put_line('l_msg_data 2 'l_msg_data);
ELSIF l_count = 3 THEN
dbms_output.put_line('l_msg_data 3 'l_msg_data);
ELSIF l_count = 4 THEN
dbms_output.put_line('l_msg_data 4 'l_msg_data);
ELSIF l_count = 5 THEN
dbms_output.put_line('l_msg_data 5 'l_msg_data);
ELSIF l_count = 6 THEN
dbms_output.put_line('l_msg_data 6 'l_msg_data);
END IF;
dbms_output.put_line('l_msg_data 'to_char(l_count)': 'l_msg_data);
ELSE
EXIT;
END IF;
END LOOP;
END IF;
Commit;
END;

Tax Calculation Disable in R12

Please make the following setup changes.
Trading Community Manager Responsibility
Trading Community: Administration > Geography Hierarchy > Query on Country Code > Manage Validations icon > Uncheck Tax Validation boxes > Save

R12 Payment Process Request - Functional and Technical Information

Payment Process Requests
Overview
Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.
There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments
Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.
Submitting a Single Payment Process Request
Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.
Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.
Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).
Click on Submit to submit the Payment process request.
Document Selection – Payables
Code: AP_AUTOSELECT_PKG
When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.
Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.
Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product
The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.
Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.
Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”
If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.
Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates
If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.
Build Payments - Payments
Code: IBY_DISBURSE_SUBMIT_PUB_PKG
Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
Primary Key: PAYMENT_SEVICE_REQUEST_ID
Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG
Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.
Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED
The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.
Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number

a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.
If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.
b) Document Validation
Code: IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..
b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.
b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.
The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review
b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c) Create Payments
Code: IBY_PAYGROUP_PUB
The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.
Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.
The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL links through payment_service_request_id.
Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments - Payments
Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id

Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments - Payables
Code: AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Direct Debits In Oracle Apps

DD are an instruction from a customer to their bank authorising an organisation to collect an amount, which may vary directly from their account.
One of the payment methods available in E-Bus along with Cash, Cheque, Credit Card.







Changes in Release 12
Oracle R12 brought some changes in Direct Debit processing and setup.
Create, Approve and Format of Automatic Receipt remain the same
Create, Approve of Remittance Batch are done AR while Formatting process has been moved to Oracle Payment
Bank Data structure are now stored in CE, IBY and HZ tables
How Flag Transaction For DD
Manual: By selecting Direct Debit receipt method and Payment Instrument
Automatic: By passing Payment_trx_extension_id and Receipt_Method during AutoInvoice creation.
Basic Direct Debit Setup in AR
Define Receipt Class and Receipt method with Remittance bank
Define Receipt Source
Assign document sequence to Receipt method defined
Set Profile option: document sequence to Always or Partially Used
Define Payment term
Define Customer Bank Account
Assign Bank account and Receipt method to Customer



R11i and R12 Bank Data Comparisons


Release 12 Release 11i
CE TABLE (Internal Bank Account) AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCOUNTS AP_BANK_BRANCHES
AP_BANK_ACCOUNT_USES_ALL
IBY TABLE (External Bank Account)
IBY_EXT_BANK_ACCOUNTS
IBY_ACCOUNT_OWNERS
HZ TABLE
HZ_PARTIES (Bank id and Bank Name)
HZ_CODE_ASSIGNMENTS (Class Category, Class Code – bank, End date Active)
HZ_ORGANIZATION_PROFILES (bank number, home country code)
HZ_CONTACTS
HZ_RELATIONSHIP (bank and bank branch)

Oracle Employee Signing/Approval Limits

There is no API and other Method To Enter Signing Limits Metalink Note (171837.1)
The only option is direct insert in base table. :)

INSERT INTO ap_web_signing_limits_all
(document_type,
employee_id,
cost_center,
signing_limit,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
org_id
)
VALUES ('APEXP',
123,
r1.cost_centre,
l_sup_amt,
SYSDATE,
gl_user_id,
gl_login_id,
SYSDATE,
gl_user_id,
201
);

Oracle contingent worker APIs

--Create
--
BEGIN
--
hr_contingent_worker_api.create_cwk
(
p_validate => FALSE
,p_start_date => rec_emp_dtls.date_of_hire
,p_business_group_id => 81 -- business group id
,p_last_name => rec_emp_dtls.last_name
,p_known_as => rec_emp_dtls.known_as
,p_middle_names => rec_emp_dtls.middle_names
,p_sex => rec_emp_dtls.gender
,p_person_type_id => 1139 --l_person_type_id
,p_email_address => rec_emp_dtls.email_address
,p_npw_number => rec_emp_dtls.employee_number
,p_first_name => rec_emp_dtls.first_name
,p_national_identifier => rec_emp_dtls.national_identifier
,p_title => rec_emp_dtls.title
,p_date_of_birth => rec_emp_dtls.date_of_birth
,p_original_date_of_hire => l_start_date--rec_emp_dtls.original_date_of_hire
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_name_combination_warning => l_name_combination_warning
,p_comment_id => l_commnet_id
,p_pdp_object_version_number => l_pdp_object_version_number
);
END;

--Update
--
BEGIN
--
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => FALSE
,p_effective_date => TRUNC(SYSDATE)
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_seas_ass_id
,p_called_from_mass_update => FALSE
,p_object_version_number => l_asg_object_version_number
,p_grade_id => NULL
,p_position_id => NULL
,p_job_id => NULL
--
,p_location_id => l_loc_id
,p_organization_id => l_org_id
--
,p_pay_basis_id => NULL
,p_segment1 => NULL
,p_segment2 => NULL
,p_segment3 => NULL
,p_segment4 => NULL
,p_segment5 => NULL
,p_segment6 => NULL
,p_segment7 => NULL
,p_segment8 => NULL
,p_segment9 => NULL
,p_segment10 => NULL
,p_segment11 => NULL
,p_segment12 => NULL
,p_segment13 => NULL
,p_segment14 => NULL
,p_segment15 => NULL
,p_segment16 => NULL
,p_segment17 => NULL
,p_segment18 => NULL
,p_segment19 => NULL
,p_segment20 => NULL
,p_segment21 => NULL
,p_segment22 => NULL
,p_segment23 => NULL
,p_segment24 => NULL
,p_segment25 => NULL
,p_segment26 => NULL
,p_segment27 => NULL
,p_segment28 => NULL
,p_segment29 => NULL
,p_segment30 => NULL
,p_concat_segments => NULL
--
,p_people_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
--
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
);
END;