ORA-20001: System Error: Procedure sort_segment_order at Step 40

ORA-20001: System Error: Procedure sort_segment_order at Step 40

DECLARE

    lc_employee_number            per_all_people_f.employee_number%TYPE; -- := 'tan_01';

    ln_person_id                  per_all_people_f.person_id%TYPE;

    ln_assignment_id              per_all_assignments_f.assignment_id%TYPE;

    ln_object_ver_number          per_all_assignments_f.object_version_number%TYPE;

    ln_address_id                 PER_ADDRESSES.ADDRESS_ID%TYPE;

    ln_asg_ovn                    NUMBER;

    ld_per_effective_start_date   per_all_people_f.effective_start_date%TYPE;

    ld_per_effective_end_date     per_all_people_f.effective_end_date%TYPE;

    lc_full_name                  per_all_people_f.full_name%TYPE;

    ln_per_comment_id             per_all_people_f.comment_id%TYPE;

    ln_assignment_sequence        per_all_assignments_f.assignment_sequence%TYPE;

    lc_assignment_number          per_all_assignments_f.assignment_number%TYPE;

    lb_name_combination_warning   BOOLEAN;

    lb_assign_payroll_warning     BOOLEAN;

    lb_orig_hire_warning          BOOLEAN;

   

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

    ------------Assignment

    l_assignment_id            NUMBER;

    l_effective_date           DATE := NULL;

    l_supervisor_id            NUMBER;

    lb_correction              BOOLEAN;

    lb_update                  BOOLEAN;

    lb_update_override         BOOLEAN;

    lb_update_change_insert    BOOLEAN;

    lc_dt_ud_mode              VARCHAR2 (100) := NULL;

    l_obj_version_num          NUMBER;

    l_organization_id          NUMBER := 81;

    l_soft_coding_keyflex_id   hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;

    l_concatenated_segments    VARCHAR2 (2000);

    l_comment_id               per_all_assignments_f.comment_id%TYPE;

    l_effective_start_date     per_all_assignments_f.effective_start_date%TYPE;

    l_effective_end_date       per_all_assignments_f.effective_end_date%TYPE;

    l_no_managers_warning      BOOLEAN;

    l_other_manager_warning    BOOLEAN;

    error_message              VARCHAR2 (4000) := NULL;

    current_records            NUMBER;

    total_records              NUMBER;

    error_records              NUMBER;

    l_effective_date_valid     NUMBER;

    error_message1             VARCHAR2 (4000) := NULL;  

   

    v_ledger_id              gl_ledgers.ledger_id%type;

    v_job_definition_id      PER_JOB_DEFINITIONS.job_definition_id%type;

    v_position_definition_id per_position_definitions.position_definition_id%type;   

    v_location_id            hr_locations_all.location_id%type;

    v_emp_count              number := 0;

-- Out Variables for Update Employee Assgment Criteria

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

-- ln_people_group_id  NUMBER                     := null;   -- This will cause error  ORA-20001: System Error: Procedure  at Step 40

ln_people_group_id   NUMBER                      := hr_api.g_number;   --  PEOPLE_GROUP_ID

  end;

ln_special_ceiling_step_id                          PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;

lc_group_name                                       VARCHAR2(30);

ld_effective_start_date                             PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;

ld_effective_end_date                               PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;

lb_org_now_no_manager_warning                       BOOLEAN;

lb_other_manager_warning                            BOOLEAN;

lb_spp_delete_warning                               BOOLEAN;

lc_entries_changed_warning                          VARCHAR2(30);

Entering: HR_ASSIGNMENT_BK3.UPDATE_EMP_ASG_CRITERIA_B                   10

  hr_api.validate_commit_unit                                           10

Leaving: HR_ASSIGNMENT_BK3.UPDATE_EMP_ASG_CRITERIA_B                   20

Entering hr_kflex_utility set_profiles                                  5

hr_kflex_utility set_profiles                                           10

hr_kflex_utility set_profiles                                           20

hr_kflex_utility set_profiles                                           30

hr_kflex_utility set_profiles                                           40

hr_kflex_utility set_profiles                                           50

Leaving hr_kflex_utility set_profiles                                   100

Entering hr_kflex_utility set_session_date                              5

hr_kflex_utility set_session_date                                       30

Leaving hr_kflex_utility set_session_date                               100

Entering:upd_or_sel_keyflex_comb                                        10

upd_or_sel_keyflex_comb                                                 20

Entering:check_ignore_varray                                            10

Seg3 $Sys_Def$                                                         101

Entering:ins_or_sel_keyflex_comb                                        10

ins_or_sel_keyflex_comb                                                 20

Entering:check_ignore_varray                                            10

ins_or_sel_keyflex_comb                                                 30

Entering:sort_segment_order                                             10

sort_segment_order                                                      20

sort_segment_order                                                      30

sort_segment_order                                                      40

ins_or_sel_keyflex_comb                                                 100

Leaving upd_or_sel_keyflex_comb                                         140

-----RollBack ORA-20001: System Error: Procedure sort_segment_order at Step 40

Cause:        The procedure sort_segment_order has created an error at Step 40. 

Action:        Contact your system administrator quoting the procedure sort_segment_order and Step 40.

i found the solution which is to add at least one value to the key flexfield : " People Group Flexfield" .

Regards,

Top Ten Unix Commad

Command	Top Ten Unix Commad	Description
1. ls	ls ls -alF	Lists files in current directory List in long format
2. cd	cd temp cd ..	Change directory to temp Move back one directory
3. mkdir	mkdir test	Make a directory called test
4. rmdir	rmdir deletedDir	Remove directory (must be empty)
5. cp		cp file2 copiedfile cp file1 file1.bak
6. rm	rm file1.bak rm *.txt	Remove or delete file Remove all file
7. mv	mv oldfile.html newfile.html	Move or rename files
8. more	more test.html	Look at file, one page at a time
9. lpr	lpr index.html	Send file to printer
10. man	man ls	Online manual (help) about command

Enable close Button in Oracle forms R12

In Program units You will have APP_CUSTOM Package 
Change the code in procedure as required to close_window of APP_CUSTOM 
package to handle close button 

For example like 
if (wnd = '<your_window_name>') then 
app_window.close_first_window; 
elsif (wnd = '<another window>') then 
--defer relations 
--close related windows 
null; 
elsif (wnd = '<yet another window>') then 
--defer relations 
--close related windows 
null; 
end if;

Bahaa Barghouti 
Thanks,

How to Change SYS and SYSTEM Passwords

To change the SYS and SYSTEM passwords, perform the following steps:

1. Log into Oracle as internal or sysdba

$ sqlplus  / as sysdba

2. Type the following commands:

ALTER USER SYS IDENTIFIED BY [password];
ALTER USER SYSTEM IDENTIFIED BY [password];

Reference: 

(Doc ID 1051982.6)

CST: Accrual Load Data Purge

CST: Accrual Load Data Purge (Doc ID 1613969.1)

SOLUTION

Development has provided a concurrent request via Patch 17904883:R12.BOM.C for this issue. Following are the steps to download and run the Patch.

Patch Application

A. For 12.1:

1. Download and review the readme for Patch: 17904883:R12.BOM.C.
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. This patch delivers the following files:

CSTACCLB.pls           120.27.12010000.51
CSTACCLS.pls           120.0.12010000.5
cstaldp17904883cp.ldt  120.0.12010000.2

B. For 12.2:

1. Download and review the readme for Patch: 20184781:R12.BOM.D.
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. This patch delivers the following files:

CSTACCLB.pls  120.62.12020000.21  
CSTACCLS.pls  120.1.12020000.4
bomprg.ldt  120.150.12020000.20 

C. Carefully review the Warning below and Sample Use Case before proceeding.

Using adop hotpatch oracle 12.2.4

Example:

unzip -q p20049475GENERIC.zip -d  /fs_ne/EBSapps/patch

adop phase=apply patches=20184781 hotpatch=yes

API to create Bank information in Oracle R12

/* This is API to create Bank information in Oracle R12 

*/

DECLARE

    xx_output          VARCHAR2 (1500);

    xx_msg_dummy       VARCHAR2 (1500);

    xx_return_status   VARCHAR2 (1500);

    xx_msg_data        VARCHAR2 (1500);

    xx_bank_id         NUMBER;

    xx_msg_count       NUMBER;

    xx_extbank_rec     apps.iby_ext_bankacct_pub.extbank_rec_type;

    xx_response_rec    apps.iby_fndcpt_common_pub.result_rec_type;

BEGIN

    xx_return_status := '';

    xx_msg_count := '';

    xx_msg_data := '';

    xx_extbank_rec.bank_name := 'Bank Name';

    xx_extbank_rec.bank_number := 'TEST123';

    xx_extbank_rec.country_code := 'PS';

    apps.fnd_msg_pub.delete_msg (NULL);

    apps.fnd_msg_pub.initialize ();

    iby_ext_bankacct_pub.create_ext_bank (

        p_api_version     => 1.0,

        p_init_msg_list   => fnd_api.g_true,

        p_ext_bank_rec    => xx_extbank_rec,

        /* Return Values ypu can use*/

        x_bank_id         => xx_bank_id,

        x_return_status   => xx_return_status,

        x_msg_count       => xx_msg_count,

        x_msg_data        => xx_msg_data,

        x_response        => xx_response_rec);

    xx_output := ' ';

    IF (xx_return_status <> 'S')

    THEN

        FOR i IN 1 .. xx_msg_count

        LOOP

            apps.fnd_msg_pub.get (i,

                                  apps.fnd_api.g_false,

                                  xx_msg_data,

                                  xx_msg_dummy);

             xx_output :=xx_output || (TO_CHAR (i) || ': ' || SUBSTR (xx_msg_data, 1, 250));

        END LOOP;

         DBMS_OUTPUT.put_line ('Error in Creating Bank: ');

    ELSE     

     DBMS_OUTPUT.put_line (' Success !!');

    END IF;

   --- COMMIT;

EXCEPTION

    WHEN OTHERS

    THEN

        ROLLBACK;

        DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);

END;

/

Get Segment Description Oracle R12

SELECT SUBSTR(gl_flexfields_pkg.get_description_sql( :chart_of_account_id,:segment_number,:segment_value),1,500) segment_desc from dual
/

API to Create AP Supplier Site

API - pos_vendor_pub_pkg.create_vendor_site

DECLARE
 l_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
 lc_return_status         VARCHAR2(10);
 ln_msg_count            NUMBER;
 lc_msg_data               VARCHAR2(1000);
 ln_vendor_site_id     NUMBER;
 ln_party_site_id         NUMBER;
 ln_location_id            NUMBER; 

BEGIN
         -- ---------------
         /* Required*?/
        l_vendor_site_rec.vendor_id               := 111;
        l_vendor_site_rec.vendor_site_code  := 'Site Name';
        l_vendor_site_rec.address_line1         := ' Main Street';
        l_vendor_site_rec.city                           := 'Palestine';
        l_vendor_site_rec.country                    := 'PS';
        l_vendor_site_rec.org_id                      := 88;

        -- --------------
                /* Optional  *?/
        -- --------------
        l_vendor_site_rec.purchasing_site_flag  :='N';
        l_vendor_site_rec.pay_site_flag                :='N';
        l_vendor_site_rec.rfq_only_site_flag       :='N'; 

        l_vendor_site_rec.TERMS_DATE_BASIS      := 'Invoice';

        l_vendor_site_rec.PURCHASING_SITE_FLAG          := 'Y';

        l_vendor_site_rec.language                      := 'ARABIC';

        pos_vendor_pub_pkg.create_vendor_site
        (
               /*-- ------------------------------ 

               Input elements 

              --------------------------------------*/
              p_vendor_site_rec    => l_vendor_site_rec,
             /*
              -- Output elements
             */
              x_return_status         => lc_return_status,
              x_msg_count             => ln_msg_count,
              x_msg_data                => lc_msg_data,
              x_vendor_site_id      => ln_vendor_site_id,
              x_party_site_id         => ln_party_site_id,
              x_location_id            => ln_location_id
        );

        COMMIT; 

 EXCEPTION
         WHEN OTHERS THEN
                       ROLLBACK;
                       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/ 

Select Suppliers Details in R12

How to select Suppliers Details in R12:

SELECT  asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
asp.vendor_name_alt  "Supplier Name Alt",
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,asp.vat_registration_num vendor_tax_number ,ass.vat_registration_num site_vendor_tax
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND ass.org_id = hou.organization_id
--and ass.vat_registration_num is not null

order by to_number(asp.segment1) ;