check errors in compiled code in toad

SELECT * FROM USER_ERRORS WHERE NAME='PACKAGE NAME';

FND PROFILE VALUE

To Get Organisation Id
fnd_profile.value('MFG-ORGANISATION_ID');
select fnd_profile.value('MFG-ORGANISATION_ID') from dual;
To Get responsibility id
fnd_profile.value('RESP_ID');

like wise we can get all environment variables.....................

Apps Initialize

Invoke the session before subiting any concurrent program from back end.
select the user is,responsibility id and application id to invoke the session,
select fnd.user_id ,
fresp.responsibility_id,RESPONSIBILITY_NAME,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp where fnd.user_name = 'OPERATIONS' and fresp.responsibility_name like 'Inv%';
E.g:
fnd_global.apps_initialize (user_id, resp_id, appl_resp_id);fnd_global.apps_initialize(1318,20004,51710);
example for submitting a request from back end

declare
Submit_Request boolean;
req_id number;
ln_header_id number;
ln_transaction_mode varchar2(10);
submitset_failed exception;
l_phase varchar2 (10);
l_status varchar2 (10);
l_dev_phase varchar2 (10);
l_dev_status varchar2 (10);
l_message varchar2 (10);
ls_resp_id number;
ls_user_id number;
ls_appl_resp_id number;
begin
ls_resp_id := fnd_profile.VALUE ('RESP_ID');
ls_user_id := fnd_profile.VALUE ('USER_ID');
ls_appl_resp_id := fnd_profile.VALUE ('APPL_RESP_ID');
/*Apps Initialize*/
fnd_global.apps_initialize (ls_user_id, ls_resp_id, ls_appl_resp_id);
/* Submit Reqest*/
REQ_ID := fnd_request.submit_request ('INV', 'WICTMS');
/* Module short name and program short name */
/*Checking the concurrent program and request to wait */
SUBMIT_REQUEST :=
APPS.FND_CONCURRENT.WAIT_FOR_REQUEST (request_id => req_id,
                                                                                        interval => 5 -- Sleep 5 seconds between checks.
                                                                                       ,max_wait => 0 -- Wait indefinately.
                                                                                       ,phase => 0 l_phase,
                                                                                        status => 0 l_status,
                                                                                       dev_phase => 0 l_dev_phase,
                                                                                      dev_status => 0 l_dev_status,
                                                                                      MESSAGE => 0 l_message);
  IF l_dev_status = 'NORMAL'
  THEN
 fnd_file.put_line(fnd_file.log,'Success');
 ELSE
 fnd_file.put_line(fnd_file.log,FAILED');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
fnd_file.put_line (fnd_file.LOG, 'Error in Caling Subprogram');
END;
fnd_file.put_line(fnd_file.log,'Successfully Completed');

Taking number form string

The below code will fecth the number in a string:

CREATE OR REPLACE FUNCTION GET_NUMBER_FROM_STRING(STR1 VARCHAR2) RETURN NUMBER

IS
lv_num number(20);
BEGIN
SELECT TO_NUMBER(TRIM(TRANSLATE(LOWER(STR1),'abcdefghijklmnopqrstuvwxyz/-+',' '))) INTO lv_num FROM dual;
RETURN(lv_num);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
END;


e.g
select GET_NUMBER_FROM_STRING('AD223445') from dual;

Trigger to keeep track of changes made for PL/SQL Code

Always if we use Create or replace for a pl/sql code,high chances to make chages for exsiting one.So we need to track the changes made.


CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*FROM ALL_SOURCE WHERE 1=2;

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

create a trigger to capture the chages made

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name

DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')

THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
END IF;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;

/

Data Validation Test Scripts

Data Validations is for Auditing the successful completition of implementation which had legacy data migrations:

• Check Journal Balances for a Period
• Check Supplier Trial Balances
• Checking Customer Balances
• Checking Order Counts(Open, Closed)
• Checking AR Invoice Counts(Open, Paid)
• Checking AP Invoice Counts(Open,Paid)
• Checking Inventory Onhand Balances
• Checking Item Counts by Status
• Checking Customer Counts
• Checking Vendor Counts
• Checking PO Counts

AIM Documents

Oralce AIM templates and AIM software

Good place for AIM docs www.bryanthompsononline.com
You can right click and download docs from below link.
Oracle AIM (Applications Implementation Methodology)


AIM Document List


Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

Business Requirements Definition (RD)
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

Business Requirements Mapping
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

Application and Technical Architecture (TA)
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Archtecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

Module Design and Build (MD)
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

Data Conversion (CV)
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

Documentation (DO)
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

Business System Testing (TE)
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test 

Toad Common Error

When you uprgrade to 10g db adn try to use toad 8.5 version you will not be able to view the package body.
Invalid IN parameter error will be encountered.To solve this change the view all_arguments.
and extra parameter subprogramid is added in 10g Db.and this is not supported in toad versions.

Solution:
Take a backup of all_arguments table,
create view all_arguments1 as select * from all_arguments.
create a view as all_arguments placing the subprogramid as the last column.

CREATE OR REPLACE VIEW all_arguments (owner,
                                      object_name,
                                      package_name,
                                      object_id,
                                      overload,
                                      argument_name,
                                      POSITION,
                                      SEQUENCE,
                                      data_level,
                                      data_type,
                                      DEFAULT_VALUE,
                                      default_length,
                                      in_out,
                                      data_length,
                                      data_precision,
                                      data_scale,
                                      radix,
                                      character_set_name,
                                      type_owner,
                                      type_name,
                                      type_subname,
                                      type_link,
                                      pls_type,
                                      char_length,
                                      char_used,
                                      subprogram_id
                                     )
AS
   SELECT "OWNER", "OBJECT_NAME", "PACKAGE_NAME", "OBJECT_ID", "OVERLOAD",
          "ARGUMENT_NAME", "POSITION", "SEQUENCE", "DATA_LEVEL", "DATA_TYPE",
          "DEFAULT_VALUE", "DEFAULT_LENGTH", "IN_OUT", "DATA_LENGTH",
          "DATA_PRECISION", "DATA_SCALE", "RADIX", "CHARACTER_SET_NAME",
          "TYPE_OWNER", "TYPE_NAME", "TYPE_SUBNAME", "TYPE_LINK", "PLS_TYPE",
          "CHAR_LENGTH", "CHAR_USED", "SUBPROGRAM_ID"
     FROM all_arguments1

Wip Move Transaction Migration

WIP Migration:

Create a job in Work in Process

1.       Create a job with item which is billable on and define routing sequence for the same.

E.g

Stagging Table

CREATE TABLE WIP_MOVE_TXN_STAG1

(

   CREATED_BY_NAME               VARCHAR2 (100 BYTE),

   CREATION_DATE                 DATE,

   FM_OPERATION_SEQ_NUM          NUMBER,

   FM_INTRAOPERATION_STEP_TYPE   NUMBER,

   LAST_UPDATE_DATE              DATE,

   LAST_UPDATED_BY_NAME          VARCHAR2 (100 BYTE),

   PRIMARY_ITEM_ID               NUMBER,

   ORGANIZATION_ID               NUMBER,

   PROCESS_PHASE                 NUMBER,

   PROCESS_STATUS                NUMBER,

   TO_OPERATION_SEQ_NUM          NUMBER,

   TO_INTRAOPERATION_STEP_TYPE   NUMBER,

   TRANSACTION_DATE              DATE,

   TRANSACTION_QUANTITY          NUMBER,

   TRANSACTION_UOM               VARCHAR2 (10 BYTE),

   TRANSACTION_TYPE              NUMBER,

   WIP_ENTITY_ID                 NUMBER)

Sample data

OPERATIONS,12-Jan-2011,10,1,12-Jan-2011,OPERATIONS,43900,207,1,1,20,1,12-Jan-2011,1,Ea,1,137792

OPERATIONS,12-Jan-2011,20,1,12-Jan-2011,OPERATIONS,43900,207,1,1,30,1,12-Jan-2011,1,Ea,1,137792

OPERATIONS,12-Jan-2011,30,1,12-Jan-2011,OPERATIONS,43900,207,1,1,30,3,12-Jan-2011,1,Ea,1,137792

Procedure to fill interface table

CREATE OR REPLACE PROCEDURE WIP_MOVE_TXN(ERRBUFF OUT VARCHAR2,RETCODE OUT NUMBER) IS

CURSOR WIP_CUR IS SELECT * FROM WIP_MOVE_TXN_STAG1;

BEGIN

     FOR WIP_REC IN WIP_CUR LOOP

     INSERT INTO WIP_MOVE_TXN_INTERFACE

                  (

                  PRIMARY_ITEM_ID,

                    ORGANIZATION_ID,

                    PROCESS_PHASE,

                    PROCESS_STATUS,

                    WIP_ENTITY_ID,

                    TRANSACTION_DATE,

                    TRANSACTION_QUANTITY,

                   TRANSACTION_UOM,

                    TRANSACTION_TYPE,

                    FM_OPERATION_SEQ_NUM,

                    FM_INTRAOPERATION_STEP_TYPE, 

                    TO_OPERATION_SEQ_NUM,

                    TO_INTRAOPERATION_STEP_TYPE,

                    CREATED_BY_NAME,

                    CREATION_DATE, 

                    LAST_UPDATE_DATE,

                    LAST_UPDATED_BY_NAME

                 --    group_id 

                 ) VALUES

                 (

                  WIP_REC.PRIMARY_ITEM_ID,

                    WIP_REC.ORGANIZATION_ID,

                    WIP_REC.PROCESS_PHASE,

                    WIP_REC.PROCESS_STATUS,

                    WIP_REC.WIP_ENTITY_ID,

                    sysdate,

                    WIP_REC.TRANSACTION_QUANTITY,

                    WIP_REC.TRANSACTION_UOM,

                    WIP_REC.TRANSACTION_TYPE,

                    WIP_REC.FM_OPERATION_SEQ_NUM,

                    WIP_REC.FM_INTRAOPERATION_STEP_TYPE, 

                    WIP_REC.TO_OPERATION_SEQ_NUM,

                    WIP_REC.TO_INTRAOPERATION_STEP_TYPE,

                    WIP_REC.CREATED_BY_NAME,

                    sysdate, 

                    sysdate,

                    WIP_REC.LAST_UPDATED_BY_NAME

                    --  WIP_REC.group_id

                 ); 

 

     END LOOP;

END WIP_MOVE_TXN;

/

set serveroutput on

declare

  errbuff varchar2(200);

  retcode varchar2(10);

begin

  WIP_MOVE_TXN(errbuff,retcode);

  dbms_output.put_line('Procedure executed successfully');

  commit;

end;

/

And now  go to inventory ->setup->trasaction->interface Manager

Choose move transaction and select tools and launch manager to run move transaction concurrent program.

Find it request it should invoke work order program with transaction_id to validate.

If transaction is validated data from interface table will be moved to base table

Tables to check

create table xx_WIP_MOVE_TXN_INTERFACE as select * from WIP_MOVE_TXN_INTERFACE

     

     

     SELECT * FROM  WIP_TXN_INTERFACE_ERRORS --WHERE TRANSACTION_ID=15264055

     

      select * from WIP_MOVE_TXN_INTERFACE where primary_item_id='6707'

     

      select * from wip_move_transactions where TRANSACTION_ID='15264087'

     

      select * from wip_discrete_jobs_v where wip_entity_name='92839'

     

      select * from wip_move_transactions where wip_entity_id='137791'

     

      select * from wip_operations where wip_entity_id='121750'