SLA Data Flow and Table Links

Inventory Subledger Accounting (SLA) Data Flow Chart

Subledger Accounting (SLA) Data Flow Chart

Important columns affected:

After a transaction is performed in forms:
mtl_material_transactions.costed_flag = 'N'

After the Cost Manager Picks up the data and processes it:
mtl_material_transactions.costed_flag is Null
xla_events.event_status_code = 'U'
xla_events.process_status_code = 'U'

After the Create Accounting - Cost Management is run:
xla_events.event_status_code = 'P'
xla_events.process_status_code = 'P'
xla_ae_headers.gl_transfer_status_code = 'N'
xla_ae_headers.gl_transfer_date is Null

After the Transfer To GL is run:
xla_ae_headers.gl_transfer_status_code = 'Y'
xla_ae_headers.gl_transfer_date is Not Null


Queries involved:
 1.select * from mtl_material_transactions where transaction_id = '&transaction_id'
 2.select * from mtl_transaction_accounts where transaction_id = '&transaction_id'
 3.select * from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'
 4.select * from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id')
 5.select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id from mtl_transaction_accounts where transaction_id =

'&txnid')
 6.select * from xla_ae_headers where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select

inv_sub_ledger_id from mtl_transaction_accounts where transaction_id = '&txnid'))
 7.select * from xla_ae_lines where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id

from mtl_transaction_accounts where transaction_id = '&txnid'))
 8.select * from gl_import_references where gl_sl_link_table = 'XLAJEL' and gl_sl_link_id in ()
 9.select * from gl_je_lines where je_header_id in () and je_line_num in ('')
 10.select * from xla_accounting_errors where event_id in (select event_id from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'))

Navigation to Add/Edit OM Cancel Reson Codes

Using the below navigation in OM, We can add or end date the cancel reasons
Setup -> Quick Codes->Order Management-> CANCEL_CODE

Oracle Configurable Home Page

Applies to:
Oracle Applications Framework - Version 12.1.2 to 12.1.2 [Release 12.1]
Information in this document applies to any platform.
Goal
This document aims at giving the reader information on the new "configurable homepage' feature delivered in 12.1.2

Fix
What is the Configurable Home Page

In release 12.1.2 the E-Business Suite homepage has been re-designed using the configurable pages infrastructure. This allows highly flexible content and layout including the ability to add and move content around the homepage to suit your needs.
Furthermore, this enhancement allows customers to work with a WYSIWYG model of configuration.

The configurable home page also includes a new and improved navigator tree which has expandable menus using richer and faster ajax calls and requests are kept within the sidebar to save valuable homepage space. The new tree also eliminates full page render of server-side requests therefore enhancing performance.

Another important change to note is that the favorites have moved from the main body of the homepage to the global header at the top of the page resulting in more realestate being available where you need it.

The image below demonstrates the new homepage which includes the expandable menu and drop down favorite features.

This is the Navigation menu appearing in the header. Note: this ONLY appears when you navigate away from the homepage. It allows users to quickly and easily access other functions without needing to navigate back to the homepage.

How do you enable the configurable homepage?
 For pre-12.1.3 environments set profile option 'Enable Configurable HomePage' to Yes. For 12.1.3 environments set profile FND: Disable Configurable Home Page to false.

Enable Configurable Homepage / FND_CONFIG_HOMEPAGE_ENABLED, is no longer used, but is retained for Release 12.1.2 backwards compatibility.
 From 12.1.3 the configurable home page behaviour is controlled by FND: Disable ConfigurableHome Page /FND_CONFIG_HOMEPAGE_DISABLED profile option.

As mentioned above the new homepage also has the ability to enable slideout menus. This includes a slideout (down) navigator menu which allows users to navigate to functions without having to move back to the homepage as well as slideout favorites. This results in better system performance.
 To  enable the slideout menus the Accessibility Mode must be set to None and profile option "FND Slideout menu" must be set to enabled. Note: profile option "FND Slideout menu"  is no longer used on the 12.1.3 code level but is retained for 12.1.2 backwards compatibility. On 12.1.3 to disable the navigator and favorites pull-down menu set profile "FND: Disable Navigator and Favorites Rich Menu" to True and "FND Slideout menu" to Disabled

What other features are available?
 The new homepage feature also includes a WYSIWYG approach to personalization on the homepage. This is demonstrated in the image below. Here one can re-arrange, re-order, rotate , add and remove content on the homepage to suit their needs. Select the 'personalize page' link in the header region of the page to enter the WYSIWYG personalization view.

Expandable/Collabsible Menus
 One can also enable Expandable menus. If for example you had the worklist displayed on the homepage you can personalize the page to enable the worklist to be hidden or expanded. See images below.
 Before the change to expandable menus. Notice the worklist cannot be collapsed.

After the change to expandable menus for the worklist. Notice that the worklist is now collapsible .

To change a region so that it uses expandable/collapsible menus change the settings for the Flexible Content: Notifications Content region as follows:

Show Header = true
 User Operations = disclose
 User Personalization = true

Adding Scroll Bars To A Region
 One has the ability to add scroll bars to regions on the homepage so that users can manage the space usage on the home page effectively. The basic principle of this feature is that if the data within the region exceeds the window height or width property of the region the scrolling bar will appear automatically.
 To enable the scroll bar simply adjust the regions height and width properties to a smaller value (in pixels) to force scroll bars to render.
 Select the Personalize Page link - Choose Personalization Level 'Site' - select the personalize pen icon - personalize the flexible layout. Adjust the height and with properties

After changing the height property of the Worklist region to 300. Notice the scrollbar to the far right of the Worklist



Adding New Content/Regions
 Any standalone region can be added to the homepage. A standalone region is defined as a region that can run on its own without ANY dependancies. It is also possible to create your own standalone regions at add them to the homepage
 To add a region to the homepage follow these steps. I will use an example here of a seeded region.
 Select the Personalize Page link - Choose Personalization Level 'Site' and GO - select the Content tab - scroll to the bottom of the page and select the add content '+' icon - select the create content button - enter an id e.g. ExtAncContent, enter a title e.g. Extended Anc Content - enter a full name e.g. /oracle/apps/fnd/framework/toolbox/labsolutions/webui/ConfigHomePG.AncContainRN -
apply the changes - select the title just created - apply the changes. Now review the change as it appears on the homepage.

Note: If the Content tab does not appear in release 12.1.3, make sure that the profile option "Self Service Accessibility Features" is set to null at ALL levels.

Shows a new region added via the steps above


Standalone regions can now be identified via a new tool called the 'portlet generator'. Once identified these can be added to the home page as discussed above. You can read more about this tool in Chapter 16 in the following document:
 121sacg.pdf

Removing the notification list from the homepage
1. Set profile 'Enable Configurable HomePage' to Yes
2. Set profile 'Personalize Self-Service Defn' to Yes
3. Stop/Start the oacore opmn process
4. Login to the homepage
5. Select the Personalize "Worklist" link
6. Select the pen icon next to "Default Single Column: Worklist"
7. Select rendered = False at the Site level
8. Save
9. Select return to application
The worklist should now be gone.

To enable the notification list when the configurable homepage is disabled ('Enable Configurable HomePage' = No,  'FND:Disable Configurable Home Page = True) render the Row Layout:Home Content Row item to true from the Home Page personalization screen.

Removing the Enterprise Search Feature
 1. Login to the environment with the user who has administrator privileges.
 2. Select "Functional Administrator" responsibility.
 3. Select "Personalization" menu
 4. On the "Application Catalog" page set "Document Path" to "/oracle/apps/fnd/search/webui/AppsSearchRG" and click "Go".
 5. Click on "Personalize page" icon.
 6. Set Function value to "Applications Configurable Home Page" i.e. OANEWHOMEPAGE and then click on Apply.
 7. Select "Personalize" icon for Row Layout region. Set the rendered property to false at function level.
 8. Navigate back to the page and see the changes applied.

Reverting back to the 12.1 Homepage Layout
 Set the following profiles:
 FND: Applications Navigator Menu Consolidation count : 25
 FND Slideout menu: Enabled
 Enable Configurable HomePage: No
 FND: Personalization Region Link Enabled: Yes
 FND: Disable Configurable Home Page: True

* The above changes will result in the expandable menus changing to the older 'link style' menus
Changing the browser page name for the Home Page
This option is not specific to the new homepage. In fact, it can be performed on versions 11i, 12.0.X and 12.1.X
By default the browser displays the homepage as 'Oracle Applications Home Page'
You may want to change this so that when working with multiple environments at the same time it is easy to distinguish via the browser tabs (Firefox or Internet Explorer) which environment is which.

To make the change, login to the homepage and select the 'personalize page' link at the top right of the page. Select the pencil icon for the very first option in the list. This should be 'Page Layout: Oracle Applications Home Page'
 Change the window title at the required level to the text of your choice. Now apply and return to the application. You will notice the browser tab now displays your chosen name


Known Issues
 1. Personalize at the site level
 In the homepage personalization make sure you are doing all the personalization at the site level, as the responsibility level is not valid for the homepage. You may not get the intended result if responsibility level personalizations are present. This should be resolved by release 12.1.3
 2. Issues with responsibility tree
 The tree like responsibility tree may not expand when selecting the + sign. This functionality works in Firefox but does not work when using IE. Clicking on the actual responsibility link is the current workaround until the issue is resolved by release 12.1.3. This is discussed in bug 9709490
3. FND_NO_DATABASE_CONNECTION
Login may fail intermittently with: Application: Fnd, Message Name: oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_DATABASE_CONNECTION. We recommend that all customers apply the patch mentioned in Note 1298103.1 to avoid this issue.
4. Responsibilities sometimes do not appear
In some cases when returning to the homepage users may experience an issue where their list of responsibilities is missing. Please apply Patch 9656424 - ATGUI: TST1213:RESPONSIBILITIES NOT DISPLAYED IN NEW HOME PAGE INCONSISTENTLY to prevent this issue occurring. See Note 1316974.1 for more information
5. Slideout menu does not work
The slideout menu may fail to operate. You may see an error such as 'OAFSlideoutMenu' is undefined. Please see Note 1322619.1
6. HTTPS links added to slideout menu do not function
See Note 1351244.1 Wrong Https URL Handling In Favorites Slideout Menu
7. Locked connections after login (performance impact)

Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

ROI: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items

Goal
 Which interface tables are used to populate Serial and Lot information for Receiving Open Interface (ROI)?

Executable:RVCTP - Receiving Transaction Processor

Solution

In addition to rcv_headers_interface and rcv_transactions_interface, the mtl_transaction_lots_interface and mtl_serial_numbers_interface tables must be populated for the Receiving Open Interface (ROI) to process Delivery, Correction and Return of Serial and Lot Controlled Items.

Significant software changes were made in 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) to accommodate new ROI functionality.  Due to dependencies, the new ROI functionality will not be backported.

Example:
1) If Item is Serial Controlled only:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


2) If Item is Lot Controlled only:

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


3) If Item is both Serial and Lot Controlled:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID, --mtl_serial_numbers_interface.transaction_interface_id
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL);  --rcv_transactions_interface.interface_transaction_id



When transacting Items that are both Lot and Serial Controlled, if Quantity is greater than 1, populate mtl_serial_numbers_interface and mtl_transaction_lots_interface as follows:
1.  one record in mtl_serial_numbers_interface table for each Serial Number (or Serial Number range)
2.  for each of the mtl_serial_numbers_interface records, populate a corresponding record in mtl_transaction_lots_interface with the Lot Number associated with each mtl_serial_numbers_interface record (mtl_transaction_lots_interface.serial_transaction_temp_id=mtl_serial_numbers_interface.transaction_interface_id)

11.5.10 / R12 ROI How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

Goal
 New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher)  supports for Inter-Org Transfers:
         . RECEIVE transaction
         . DELIVER to Inventory Transaction

How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
 Solution

Following SAMPLE script is intended

To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done between 2 inventory organizations with Direct
Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)


In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.

The script will load records into the tables
          RCV_HEADERS_INTERFACE,
          RCV_TRANSACTIONS_INTERFACE,
          MTL_TRANSACTION_LOTS_INTERFACE
          MTL_SERIAL_NUMBERS_INTERFACE

If the item is only a standard item, only the records into
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables have to be created.


          ------------------------SETUP------------------------

0) Ensure to apply the patches listed in Note 367396.1 Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J) and For Release 12 see Note 423541.1


1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct

2) Item  'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and Destination Organization

SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code,
msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),
  '2','Y',
  '1','N') LOT_CONTROL,
decode(to_char(msi.serial_number_control_code),
  '1','None',
  '2','Predefined',
  '5','Dynamic at INV receipt',
  '6','Dynamic at SO issue') SERIAL_CONTROL
  from mtl_system_items_b msi,mtl_parameters mp
where msi.segment1 like '&item' and msi.organization_id=mp.organization_id;

For both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'


3) Inventory  / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1' 
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010


4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:

Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_NUM= ROI-Lot-Serial-1
        RECEIPT_SOURCE_CODE=INVENTORY
        RECEIPT_NUM=null
        SHIP_TO_ORG_ID=210

Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_LINE_ID=246486
        UNIT_OF_MEASURE=Each
        ITEM_ID=169845
        QUANTITY_SHIPPED=10
        QUANTITY_RECEIVED=0
        SHIPMENT_LINE_STATUS_CODE=EXPECTED
        SOURCE_DOCUMENT_CODE=INVENTORY
        ROUTING_HEADER_ID=3
        FROM_ORGANIZATION=207
        TO_ORGANIZATION_ID=210
        TO_SUBINVENTORY=Staging1


5)  Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534

Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         LOT_NUM=S00226
         QUANTITY=10

Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
         LOT_NUM=S00226



RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example

1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002

Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'

. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'

. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226

. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_00002'


INSERT INTO RCV_HEADERS_INTERFACE
            (HEADER_INTERFACE_ID,
             GROUP_ID,
             PROCESSING_STATUS_CODE,
             RECEIPT_SOURCE_CODE,
             TRANSACTION_TYPE,
             AUTO_TRANSACT_CODE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATION_DATE,
             CREATED_BY,
             SHIPMENT_NUM,
             SHIP_TO_ORGANIZATION_ID,
             EXPECTED_RECEIPT_DATE,
             VALIDATION_FLAG
              )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'INVENTORY', --Receipt_Source_Code
             'NEW', --Transaction_Type
             'DELIVER',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'ROI-Lot-Serial-1',   --Shipment_Num
             210,            --Ship_To_Organization_Id,
             SYSDATE,        --Expected_Receipt_Date
             'Y'             --Validation_Flag
             );
   
INSERT INTO RCV_TRANSACTIONS_INTERFACE
            (INTERFACE_TRANSACTION_ID,
             GROUP_ID,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             CREATION_DATE,
             CREATED_BY,
             LAST_UPDATE_LOGIN,
             TRANSACTION_TYPE,
             TRANSACTION_DATE,
             PROCESSING_STATUS_CODE,
             PROCESSING_MODE_CODE,
             TRANSACTION_STATUS_CODE,
             QUANTITY,
             UNIT_OF_MEASURE,
             INTERFACE_SOURCE_CODE,
             ITEM_ID,
             EMPLOYEE_ID,
             AUTO_TRANSACT_CODE,
             SHIPMENT_HEADER_ID,
             SHIPMENT_LINE_ID,
             RECEIPT_SOURCE_CODE,
             TO_ORGANIZATION_ID,
             SOURCE_DOCUMENT_CODE,
             DESTINATION_TYPE_CODE,
             SUBINVENTORY,
             SHIPMENT_NUM,
             EXPECTED_RECEIPT_DATE,
             HEADER_INTERFACE_ID,
             VALIDATION_FLAG
             )
             VALUES
             ( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID             
             rcv_interface_groups_s.currval,        --GROUP_ID
             SYSDATE,               --LAST_UPDATE_DATE
             0,                              --LAST_UPDATED_BY
             SYSDATE,               --CREATION_DATE
             0,                              --CREATED_BY
             0,                              --LAST_UPDATE_LOGIN
             'RECEIVE',               --TRANSACTION_TYPE
             SYSDATE,               --TRANSACTION_DATE
             'PENDING',              --PROCESSING_STATUS_CODE
             'BATCH',                  --PROCESSING_MODE_CODE
             'PENDING',              --TRANSACTION_STATUS_CODE
             2,                              --QUANTITY
             'Each',                       --UNIT_OF_MEASURE
             'RCV',                       --INTERFACE_SOURCE_CODE
             169845,                    --ITEM_ID
             13706,                      --EMPLOYEE_ID
             'DELIVER',              --AUTO_TRANSACT_CODE
             233534,                    --SHIPMENT_HEADER_ID
             246486,                    --SHIPMENT_LINE_ID
             'INVENTORY',        --RECEIPT_SOURCE_CODE
             210,                          --TO_ORGANIZATION_ID
             'INVENTORY',        --SOURCE_DOCUMENT_CODE
             'INVENTORY',        --DESTINATION_TYPE_CODE
             'Staging1',                 --SUBINVENTORY
             'ROI-Lot-Serial-1',    --SHIPMENT_NUM
             SYSDATE,               --EXPECTED_RECEIPT_DATE,
             rcv_headers_interface_s.currval,  --HEADER_INTERFACE_ID
             'Y'                            --VALIDATION_FLAG
             );


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            LOT_NUMBER,
            TRANSACTION_QUANTITY,
            PRIMARY_QUANTITY,
            SERIAL_TRANSACTION_TEMP_ID,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE, --LAST_UPDATE_DATE
            0,                --LAST_UPDATED_BY
            SYSDATE, --CREATION_DATE
            0,                --CREATED_BY
            0,                --LAST_UPDATE_LOGIN
            'S00226',    --LOT_NUMBER
            2,                --TRANSACTION_QUANTITY
            2,                --PRIMARY_QUANTITY
            MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            FM_SERIAL_NUMBER,
            TO_SERIAL_NUMBER,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID)
            VALUES
            (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE,  --LAST_UPDATE_DATE
            0,                 --LAST_UPDATED_BY
            SYSDATE,  --CREATION_DATE
            0,                 --CREATED_BY
            0,                 --LAST_UPDATE_LOGIN
            'SM1_00001', --FM_SERIAL_NUMBER
            'SM1_00002', --TO_SERIAL_NUMBER
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
Commit;

Note: On R12 instance, RHI.org_id or RTI.org_id should also be populated.


2) Run the following scripts to check data have been correctly inserted

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839

SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839


3) In Purchasing Responsibility, Change to receiving organization and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=25168)


4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 has Receive and Deliver transactions.


5) Check how the following application tables have been populated/updated

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
             
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012

SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED

SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=307969
    DESTINATION_TYPE_CODE=RECEIVING
    PARENT_TRANSACTION_ID=-1
    QUANTITY=2

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=307970
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=307969
    QUANTITY=2

Technical details Payment Process Request (PPR) processing in Payments in R12

Technical details Payment Process Request (PPR) processing in Payments in R12
 
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

When clicking on the View Output button, the window opens up and disappear immediately

Problem Symptoms/Problem Statement:
Concurrent request completes normal.
Blank fade on the concurrent request window, which is a result of windows, opens and disappears automatically in
fraction of second after pressing the View Output button.
Report Completed Successfully but unable to view output.
When clicking on the View Output button, the window opens up and disappear immediately.

Steps To Reproduce:
Run the Report click on the View Output button, the window opens up and disappears.

Cause
It is due to Internet Explorer security Options.

Solution
To implement the solution, please execute the following steps:

Internet Explorer>Security Options>Tools>Internet Options> Security> Custome Level
1. Automatic Prompt for File Download- Should be Enable
2. File Download-Should be Enable.

Query to display Compentencies related to Employee

Query to display Compentencies related to Employee in Performance Appraisals

SELECT pap.person_id, pap.full_name appraiser, pc.NAME compentency,
       NVL2 (prl1.NAME,
             prl1.step_value || '-' || prl1.NAME,
             prl1.step_value
            ) AS emp_prof_level,
       pce.comments, prl1.step_value
  FROM per_competence_elements pce,
       per_rating_levels_vl prl1,
       per_rating_levels_vl prl2,
       per_rating_levels_vl prl3,
       per_all_people_f pap,
       per_competences pc
 WHERE pce.proficiency_level_id = prl1.rating_level_id(+)
   AND pce.rating_level_id = prl2.rating_level_id(+)
   AND pce.weighting_level_id = prl3.rating_level_id(+)
   AND object_id = pap.person_id
   AND pc.competence_id = pce.competence_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND effective_end_date
   AND pce.object_name = 'ASSESSOR_ID'
   AND pce.object_id IN (
          SELECT perp.person_id
            FROM per_participants perp, per_assessments pass
           WHERE perp.participation_in_id = pass.appraisal_id
             AND perp.participation_in_table = 'PER_APPRAISALS'
             AND perp.participation_in_column = 'APPRAISAL_ID'
             AND pass.assessment_id = pce.assessment_id
             AND pass.appraisal_id =
                          74515
                               ---(Appraisal id receieved from previous query)
                               );

Updating Performance Management Plan End Date

The ability to reopen a PMP after it has been completed is available in R12.1.3, but not in the earlier releases.

Updating Performance Management Plan Appraisals After the Appraisal Task End Date (Doc ID 1207165.1)
Oracle Performance Management provides a new profile option
HR: Update PMP Appraisals
After the Appraisal Period (HR: PER_WPM_APPRAISAL_UPDATE_AFTER_PERIOD.). Using this new profile option, Enterprises can decide if they want their workers to complete PMP Appraisals after the Appraisal Task End Date and before the Plan Period End Date.
This profile option can be set at the Responsibility and User levels.
If the profile is set to Yes:
Then Managers and Workers can update PMP Appraisals after the Appraisal Task End Date.
If the profile option is set to No or is left blank:
Then Managers and Workers cannot update PMP Appraisals after the Appraisal Task End Date.
There is no default value for this profile option.
Note: This profile option applies to PMP Appraisals only.

This profile option is available from R12.1.3.

However, in lower versions there has been the request for the same thing. Since the functionality is not currently available within the lower version applications a datafix was required.

Need To Extend PMP Appraisal Dates Beyond The The Plan Dates (Doc ID 1314529.1)

The following SQL identified the invalid / incorrect data:
SELECT plan_id
FROM per_perf_mgmt_plans
WHERE plan_name like '&PLAN_NAME'; --replace &PLAN_NAME with your plan_name.

SELECT end_date
FROM per_perf_mgmt_plans
WHERE plan_id = &plan_id;

SELECT task_end_date
FROM per_appraisal_periods
WHERE plan_id = &plan_id;

=== Solution /Action Plan ===

To implement the solution, please execute the following steps on a TEST instance first:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:
i) To update the PLAN Period (IF you need both Plan and Appraisal periods need to be updated, update the plan period first):
UPDATE per_perf_mgmt_plans
SET end_date = '' --example '17-APR-2013'
WHERE plan_id = '';

iii) Use the below DML to update the task end date for Appraisals ONLY:
UPDATE per_appraisal_periods
SET task_end_date = '' --example '17-APR-2013'
WHERE plan_id = ''
AND task_end_Date = ''; -- Example: 10-APR-2013 which is the current task end date


3. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:

select * from per_perf_mgmt_plans
where plan_id =''

4. If you are satisfied with the results, issue a commit.

5. Confirm that the data is corrected when viewed in the Oracle Applications by querying the Plan Details :
NAV: HR Professional v4.0 > Performance Management > Performance Management Plan > Query up the plan

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

Is it Possible to Update Generate Employee Number Method From Automatic to Manual

Applies to:

Oracle Human Resources - Version: 11.5.10.0 to 12.1.1 - Release: 11.5 to 12.0

Information in this document applies to any platform.

Is it possible to move back to the manual employee numbering?

What is the impact for the persons already entered in the system?

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Solution

The only implication would be to check/keep track of any duplication numbers.

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Please find detailed below the steps to change the automatic to manual employee

number generation.

When creating an employee the system automatically inputs the employee number.

This value is always numeric. But some Business reasons could require that

the employee number to be alphanumeric or the employee number should be entered

manually.

1. In the standard Responsibility US HRMS Manager navigate to:

Work Structures -> Organization -> Description.

2. Query back the Business Group.

3. Click on the Organization Classification of business group.

4. Click on the [Others] button.

5. From the List of values (LOV) click on Business Group Information.

6. Click in the Flexfield box.

7. This will bring up a window called Business Group Information.

One of the fields is Employee Number Generation. This should currently say

Automatic.

8. Update this to say Manual.

9. Save the record.

To test the results.

1. In the standard responsibility US HRMS Manager navigate to:

People -> Enter and Maintain -> Create a new person.

2. Now the employee number is NOT generated automatically.

Fixed Assets Additions API Sample Script

Sample Script: Using the Additions API via Invoices

The following sample script shows how you can use the Additions API via invoices:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description            := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                              (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API with No Invoices

The following sample script shows how you can use the Additions API to test a manual change, when no invoice information is used:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;

   l_return_status            VARCHAR2(1);     
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description                  := '&description';
   l_asset_desc_rec.asset_key_ccid               := null;

   -- cat info 
   l_asset_cat_rec.category_id                   := &category_id

   --type info
   l_asset_type_rec.asset_type                   := '&asset_type';

   -- fin info
   l_asset_fin_rec.cost                          := &cost
   l_asset_fin_rec.date_placed_in_service        := '&DPIS';
   l_asset_fin_rec.depreciate_flag               := 'YES';

   -- deprn info
   l_asset_deprn_rec.ytd_deprn                   := &ytd
   l_asset_deprn_rec.deprn_reserve               := &reserve
   l_asset_deprn_rec.bonus_ytd_deprn             := 0;
   l_asset_deprn_rec.bonus_deprn_reserve         := 0;

   -- book / trans info
   l_asset_hdr_rec.book_type_code                := '&book';

   -- distribution info
   l_asset_dist_rec.units_assigned               := 1;
   l_asset_dist_rec.expense_ccid                 := &ccid
   l_asset_dist_rec.location_ccid                := &location_id
   l_asset_dist_rec.assigned_to                  := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                           := l_asset_dist_rec;

   -- call the api 
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;

   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');

dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API via Invoices with Alternative Ledger Currency

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description             := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- rate info
   l_inv_rec.inv_rate_tbl(1).set_of_books_id  := 102;
   l_inv_rec.inv_rate_tbl(1).exchange_rate    := 2;
   l_inv_rec.inv_rate_tbl(1).cost             := 2001;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                    (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/