Tuesday, 7 July 2015

LCM(Landed cost management) as Pre-Receiving Transaction flow step by step guide

PRE-RECEIVING LCM TRANSACTION FLOW STEPS

Step1: Responsibility: Purchase Order Super User.
           Navigation: Oracle Purchasing--> Purchase Order--> Purchase Orders.
            Create Purchase Order with LCM enabled Organization.
            Supplier: MATERIAL SUPPLIER
            Supplier Site: Chennai
            Ship to: ICD_LCM_Org (LCM enabled Org.)
            Line: (Item): RMLCM00002
            (Make sure Inventory Asset flag = Y in this org )
            Quantity: 1 
            Price: 2000 (INR)
            Receipt Routing = “Standard Receipt”                                 
            Match Approval Level = “2-Way”                         
            Invoice Match Option =“Receipt”->it is mandatory for LCM actual management      
            Destination type =  “Inventory”    
            Save and Approve the Purchase Order.  



    
 Step 2: Responsibility : Landed Cost Management.
             Navigation:  Workbench --> Shipments
             Header:Choose the LCM Enabled Inventory Organization(ICD_LCM_Org)
             Create a new Shipment
             Choose the Operating Unit 
             Choose the Shipment Type
             Select the Receiving Location and Shipment Date
             Line Group: Select the Group .
             Enter a Group Reference : Material Shipment
             Choose the Source Type: Purchase Order
             Third Party: Choose the Supplier of the Purchase Order
             Third Party Site: Chennai (Supplier Site)
             Click on Save and then Apply.
             Displays
             Confirmation         
             The Shipment 1 has been saved successfully.
             Click on Lines Tab
             Choose the Approved Purchase Order  and Click on Go.
             Select the Line , Click on Save and Apply.




Step 3: Choose the Validate option from the Actions
             Choose the Manage Charges Option  From Actions and then click on Go.
             Charges Line : Select in the Line 1 the Type option for Freight
             Select the Third Party for Freight Charges (FREIGHT SUPPLIER)
             Select the Third Party Site (i.e. Salt Lake)
             Enter the Freight Amount
             Associations: In Type select any Type i.e Shipment Line Number.
             Similarly select for Insurance.


Step 4: Select the Validate option from Actions and then click on Go.


Step 5: Select the Calculation option from Actions and then click on Go.

Step 6: Click on View Landed Cost option from Actions and click on Go, to find out the details of the 
             Landed Cost.


     Click on the Record to get the full detailed graphical  landed cost.


Step 7: Click ‘Return to Shipment’ Link
             Choose  Actions = Submit
             Click Go
             Select Submit option from Actions and then click om Go.
             The status of the line will be changed to Completed status.

Step 8: Receive LCM Shipment
            Navigation: Oracle Purchasing Super User-->Receiving-->Receipts           
            Choose Organization
            Select Source Type = LCM
            Click LOV of Shipment




              After Receiving the purchase order deliver the item.

Step 9: Item Cost Details
             Navigation : Cost Management SLA--> Item Cost--> Item Cost History
             Select the item and Click on find.
             In the Item cost both the purchase order cost with the Freight Cost and Insurance Cost
             added in the Item cost.


Step 10:  Payable Invoice creation.
                 Navigation: Payable Super User-->Invoices-->Entry-->Invoices
                 Create a payable invoice by PO match for creating the Material PO Invoice.

   

Step 11: Another Payable Invoice is to be created for Freight Cost 
              Create a Invoice with the Freight Supplier and then match the Invoice with the Material Supplier.


     Match the Freight amount with Material Supplier with the Purchase Order and Receipt number.
     Click on Find.


 Step 12:    Select the  the Cost Factor name and enter the exact Freight amount.

                                 

  Step 13: If at the time of Invoicing the Freight Cost Increases, then after selecting Cost factor enter the 
                 new Freight charge amount. Validate the Payable Invoice.

            

 Step 14: New Freight Cost is to be updated in the Landed Cost also.
                Navigation: Landed Cost Management Responsibility--> View--> Submit New Request-->
                "Matches Interface Import ".
                 Workbench--> Shipments
                 Search the transaction , Select View Landed Cost from Actions and Click Go


   Now if the item cost is checked , it also gets updated.


  Similarly for the Insurance Supplier Invoice can be created and updated with the new cost. 





  







            



      







 




















          












       
  

  
     

Thursday, 2 July 2015

Sales Order Type Name in 11i and 12i

11i & 12i Same Query (Sales Order Type Name)
Select Distinct ot.NAME
From apps.oe_transaction_types_tl Ot,
apps.oe_order_headers_all h
Where Ot.NAME = 'KROSS LUT ORDER'
And h.HEADER_ID = :ORDER_HEADER_ID
h.order_type_id = ot.transaction_type_id

Password Retrival in Oracle Apps User

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = '&USER_NAME';

We are not able to create DFF in RCV_SHIPMENT_LINES in R-12.1.3 or How to create DFF in RCV_SHIPMENT_LINES in R-12.1.3


We are not able to create DFF in RCV_SHIPMENT_LINES in R-12.1.3
How to solved the issue:
Please find the Navigation
Navigation:
Application Developer responsibility
Flexfield > Descriptive > Register
Query on Title field for Shipment Destination Details
Select Find Name of PO_PDF_SHIP_LINES_DESTINATION autopopulates.

Select Find
Descriptive Flexfields form is displayed
Select Columns button
Attributes are listed in column.

The Attributes will all be enabled, as they are defaulted to be located within
PO_PDF_SHIP_LINES_DESTINATION.
Uncheck (disable) each Attribute you wish to use in RCV_SHIPMENT_LINES.
Then check the attributes you want to use in RCV_SHIPMENT_LINES, for those fields to be available in the Descriptive Flexfield Segment definition.
Access RCV_SHIPMENT_LINES; the Attributes should now be listed there for use.

Create Running Total Or Create Variables Values In Oracle XML Report

Running Total OR Variables Values
The variable functionality can be used to add a running total to your invoice listing report.

<?xml version="1.0" encoding="WINDOWS-1252"?>
<INVOICES>
 <INVOICE>
  <INVNUM>10001-BRIJESH</INVNUM>
  <INVDATE>1-Jan-2005</INVDATE>
  <INVOICEAMT>100</INVOICEAMT>
 </INVOICE>
 <INVOICE>
  <INVNUM>10001-TESTING</INVNUM>
  <INVDATE>10-Jan-2005</INVDATE>
  <INVOICEAMT>200</INVOICEAMT>
 </INVOICE>
 <INVOICE>
  <INVNUM>10001-REPORT</INVNUM>
  <INVDATE>11-Jan-2005</INVDATE>
  <INVOICEAMT>150</INVOICEAMT>
 </INVOICE>
</INVOICES>

And Create rtf layout and use Variables  function.

To create the Running Total field, define a variable to track the total and initialize it to 0.
Output.




DB UPGRADE 9206 TO 10203

System Overview
Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux 5 update 5
Type: Single Node instance
Applications OS User: appl11i
Database 9i OS User : db11i (same user for 9i and 10g)
9i Database Home = /d011/db11i/db/oaproddb/9.2.0
10g Database Home = /d011/db11i/db/oaproddb/10.2.0

Instance SID: DB11i
Host: demo
url:demo.conacent.com:8000
listener port:1521


Task – To Upgrade database from 9.2.0.6 to 10.2.0.3 for Oracle
Applications 11.5.10.2.


Pre Upgrade Steps

   a> Apply the patches - 5478710,4676589, 4653225
   b> Create a new home for Oracle 1 
   c> Install Oracle Database 10g.
   d> Install the companion CD.
   e> Install 10.2.0.3 patch set
   f> Create nls/data/9idata directo  
   g> Install 10g Listener


Upgrade Steps

   a> Use manual steps or dbua to upgrade the db from 9.2.0.6 to
      10.2.0.3. Manual upgrade is preferred in the industry

Post Upgrade Steps

   a> On the application tier generate appsutil.zip file
   b> Copy the appsutil.zip file generated on application tier to the         
      database tier inside 10g Oracle Home
   c> create a context file on database tier
   d> run autoconfig

Pre Upgrade Steps


a> Patches Required – 5478710, 4676589, 4653225
5478710 - TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O (December 2006)  
4676589 - ATG Roll up 4
4653225 - 11.5.10 INTEROP PATCH FOR 10GR2



Note: After apply patch 5478710(before run autoconfig) change  LD_ASSUME_KERNEL TO XD_ASSUME_KERNEL BOTH db and appl
      than runs autocfg.

###########################################################################################################

1   Before  install 10g software to change
     vi /etc/redhat-release
    change:Red Hat Enterprise Linux ES release 4 (Nahant Update 2)

2.change inventory location

     #inventory_loc=/etc/oraInventory
     inventory_loc=/d011/db11i/db/oaproddb/10.2.0/inventory

3.change oratab location:
     cat oratab
    
       DB11i:/d011/db11i/db/oaproddb/10.2.0:N

4.Install 10 g software only

5. before  companios cd installion
  set variable in bash_profile


export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
#. /d011/db11i/db/oaproddb/10.2.0/DB11i_demo.env
export PATH


ORACLE_BASE=/d011/db11i/db/oaproddb
export ORACLE_BASE

ORACLE_HOME=/d011/db11i/db/oaproddb/10.2.0/
export ORACLE_HOME

6.Install Companions cd.

Note:  In a Product to install
      Oracle Database 10g Producst 10.2.0
       

7. Install 10g patchset3

   p5337014_10203_LINUX.zip

#####################################################################################################
1.Copy dbs,appsutil,admin,.env,network/admin in 9i to 10g.

2. Replace all the file with 9.2.0 to 10.2.0.

3. set .bash_profile to new oracle_home

4.  Runs create  perl cr9idata.pl

5.   Set ORA_NLS10

   vi .env($oracle_home)10g
  if test "db920" = "db101" -o "db920" != "db102"; then

6.Apply opatch in 10g (before upgrade)
   p5648872_10203_LINUX.zip

7.Make the below changes in initTEST.ora (10g)

#optimizer_max_permutations
#row_locking
#undo_suppress_errors
#max_enabled_roles
#enqueue_resources
#sql_trace
streams_pool_size=50331648
large_pool_size = 8388608
session_max_open_files=20
optimizer_features_enable=10.2.0.3
compatible=10.2.0.

8.shutdown 9i db and 9ilistener

9.set vi .bash_profile in new home(10g)

10.oratab set old home(9.2.0)

11. upgrade database

   a.Manual

    cd $ORACLE_HOME/bin

    sqlplus “/as sysdba”

     startup upgrade

12. Create sysaux tablespace

  CREATE TABLESPACE sysaux DATAFILE '/d01/oracle/singlenode/visdata/sysaux01.dbfSIZE 1000M REUSE
  EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO
   ONLINE;

13.    Run catupgrade.sql
   cd $ORACLE_HOME/rdbms/admin
   @catupgrade.sql

  b. For dbua
     cd $ORACLE_HOME/bin
    ./dbua
############################################################################################################

Post Upgrade Steps:



Some more fixes


i) Fix Korean lexers
a. Connect to sqlplus as sysdba
b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

ii) Run adgrants.sql
a. Connect to sqlplus as sysdba
b. Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

iii) Grant create procedure privilege on CTXSYS
a. Connect to sqlplus with apps
b. Execute @$AD_TOP/patch/115/sql/adctxprv manager CTXSYS
manager is the SYSTEM password

Added few steps

i) Gather Statistics for SYS schema
a. sqlplus “/as sysdba”
b. shutdown immediate
c. startup restrict
d. @/oracle/tst10appl/admin/adstats.sql
e. shutdown
f. startup

ii) Recreate grants and synonym for apps
a. Log in to server with applmgr user
b. Execute adadmin
c. Choose -> Maintain Applications Database Entities menu
d. Choose -> Re-create grants and synonyms for APPS schema

 iii) On the application tier run the below command to generate
appsutil.zip file

 perl /a01/appl11i/appl/oaprodappl/ad/11.5.0/bin/admkappsutil.pl

iv)  Next step is to create a context file on database tier. Run the
below commands with appropriate values. This will generate context file
on database tier

$ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Note:copy jre folder in $ORACLE_HOME/appsutil to 10 oracle_home/appsutil/ than cretae context file

v) Once the context file is created on db tier, run autoconfig.

vi)start Application and Db

----------------------------------------------------------------------------------------------------------------
Login Page

-----------


   checking URL = http://demo.conacent.com:8000/OA_HTML/AppsLogin

   RW-50016: Error: - {0} was not created:
      File = {1}

apply opatch 5890966 for login page issue.





11i TO R12 UPGRADE

Database : 10.2.0.3
Application: 11.5.10.2



Step 1.  Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12


Step 2.   Run rapidwiz to create R12 code tree.

Step 3.    Move the new environment file into .bash_profile(R12)

Step 4.    Now onwards, you are connected to R12 application

-- Apply patch AD.A 4502962

Step 5.   Download the NLS Release 12 patch (4440000) for each active language in the system.

-- Run the American English upgrade patch driver
-- Takes 40 hours

Step 6.  Disable maintenance mode

Step 7.  Run admkappsutil.pl in application.

Step 8.   copy appsutil.zip to database(10g)

Step 9.   run adautocfg in db.

Step 10.  Run rapidwiz to configure and start processes.



How to update uncosted or error costed transactions for reconsideration by Cost Manager for Costing

update mtl_material_transactions
set costed_flag = 'N',
error_code = NULL,
error_explanation = NULL,
transaction_group_id = NULL,
transaction_set_id = NULL
where organization_id = &organization_id
and costed_flag in ('N','E');

How to find uncosted or error costed data in Material Transaction


select *
from mtl_material_transactions
where costed_flag in ( 'E','N');