How assign Sysadmin responsibility from back-end in oracle apps R12

 

Code 

   FND_USER_PKG is stand function 

  BEGIN

   FND_USER_PKG.ADDRESP

                       ('USERID',

                        'SYSADMIN',

                        'SYSTEM_ADMINISTRATOR',

                        'STANDARD',

                        'Add Sysadmin Responsibility to A user from Backend',

                        SYSDATE - 1,

                        SYSDATE + 99

                       );

   COMMIT;

   DBMS_OUTPUT.PUT_LINE ('Responsibility Added Successfully');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (   ' Sys Admin Resp cannot be added: '

                            || SQLCODE

                            || SUBSTR (SQLERRM, 1, 100)

                           );

END;

Oracle Workflow : Oracle Workflow upload and download and access level

 

    Oracle workflow has 3 kinds of levels.  Customization /access / Protection levels 

               A numeric value ranging from 0 to 1000. Every workflow user operates at specific access level. The access level defines whether the user can modify certain workflow data. You can only modify data that is protected at a level equal to or higher than your access level. 

                        You can change protection and customization level using options, once you have checked preserve customization then you can not do customization in workflow. once you have checkbox for lock at the access level . The protection level can change with existing access level  and access level has decide with oracle workflow access level .

                    Suppose I want to do customization in std workflow, I do not want to override my customized objects in workflow , then you have must to understand access level concept, each std oracle workflow design with 20 access level , It has checked range between customization  and  protection level, In std workflow , you have observed that range 0 - 20  0 as customization and 20 as protection level our access level is 20 , those item or objects has level 20 then once oracle patch has applied it will override existing definition.

case 1 -- Need to modify items of std workflow       

                 In such case existing oracle workflow process gets great out you can not modify because exiting std process access level is 20, you can modify up-to 20 access level only , if you want modify std workflow then you have to set access level less than 20 , it is not right practice.

                   Customization      access level    protection level

                           0                            18                      20   

   here now access level is between 0-20 range so you can modify change.

Case 2 :-   New custom process called in std workflow and not to modify  override custom process once Oracle patch applied

                While create new custom process, please update access level as 100.   and update protection level as 100 

             Customization    access level   protection level

                         0                 100                100

       access level 100 is range between customization  (0) and protection level (100), you can modify custom process.  you can not modify std process , it will be get grad out.  you can called custom process in std workflow

         Oracle Workflow Upload and Download

Oracle workflow always store in oracle database .  Oracle workflow is workflow flat file with extension .wft.  you can download workflow .wft file from database and after modification  you can upload wft file into database.  for upload and download , we have used oracle workflow utility , called as WFLOAD

   YOU HAVE TO RUN BELOW COMMAND ON UNIX     

sysntax

         WFLOAD   apps/<pwd>@EBSDEV  0 Y UPLOAD  <name.wft>

        WFLOAD   apps/<pwd>@EBSDEV  0 Y UPGRADE  <name.wft>

        WFLOAD   apps/<pwd>@EBSDEV  0 Y  FORCE  <name.wft>

    UPLOAD ---- it will be check Customization and protection level , Access level should be in range between customization and protection level

FORCE  -- It will ignore any customization and protection level.

UPGRADE -- 

      WFLOAD   apps/<pwd>@EBSDEV  0 Y DOWNLOAD < NAME.WFT>  NAME

      WFLOAD   apps/<pwd>@EBSDEV  0 Y DOWNLOAD   POAPPRV.wft POAPPRV

    

                   

Learning Oracle Cloud

 

Hi Team 

            There are 5 ways to learn oracle cloud free. you have to plan and motivate your self to study oracle cloud , there are two types of cloud positions in market, Oracle cloud technical and oracle cloud Functional.

               First way

                             Login https://support.oracle.com/portal/ and check tab Knowledge 

                here you can get lot of documentation

              2nd Way 

•     learn.oracle.com
•     https://education.oracle.com/learning-explorer#startLearning

              3rd ways 

                             https://cloudcustomerconnect.oracle.com/

               4th ways

                           doc.oracle.com

                            partner.oracle.com

                           demo.oracle.com

                            ilearning.oracle.com

                                

Oracle Golden Tax : Integrating Oracle EBS with China's Golden Tax System

  

                         Oracle Golden Tax was introduced in  Oracle Apps R12 so it is not available to prior e-business suites. Golden Tax Adaptor helps transfer information from Account Receivables into Oracle Golden Tax and send it to Golden Tax system.

                          In China there are 2 types of credit memo, Special VAT credit Memo and other one called common VAT (Value added Tax ) credit memo,  To issue a Special VAT credit memo, you need to first fill application form and get approval from tax bureau. The 16 digit number on form needs to be put into description in golden tax Adaptor, The remaining process as handle in AR invoices.

                        withing the Golden Tax system you need to maintain bank name, bank branch name and bank account number for your customers , these information should keep in Chinese

 Golden Tax System is stand alone system is used to create and print customer vat invoices whenever you sell products or services to Chinese customers.

Vat Invoice Type  can map with AR transaction types while configuring Golden Tax Adaptor .

Golden Tax System (ASINO) requires Customer name, address,tax player id, bank information,invoice reviewer's name and collector's name apart from this we also need to setup the invoice amount limit, number of limit,etc in oracle system 

Deposit Golden Tax Invoice  : there is not any VAT involved, so there is no VAT with Golden system.In oracle Financials AR you can define transaction types which will interface to the  golden tax System.

you can define custom transfer rules , The rules helps to prevent unwanted AR transaction types transfer into golden tax modules. 

Programs used in golden Tax

    Receivables Transfer to Golden Tax Adaptor    

•   Golden Tax Invoice Purge -- used to purge golden Tax Invoices
•   Golden Tax Invoice Mapping Report
• 
  

Business Email : Situation Sentences

 

   Hello Friends,

                         In last session, we  had been talked about e-mail format,  We are discussing about email sentences with respective situation.

• 1.  Need send clarification mail
• 2.  Need send acknowledge mail
• 3.  Need send a attachment
• 4.  Provide answer on questions
• 5.  Asking confirmation or request to check
• 6.  Asking any help required
• 7.  Apologies for delay
• 8.  Avoid miscommunication
• 9.  Refer a meeting discussion

                   

Oracle WEBADI : Registration Web-adi Table

   Create table script 

     CREATE TABLE BNE.XXST_GOLD_IMPORT_INT_BNE_T

(

  OPERATING_UNIT        VARCHAR2(100),

  SOURCE                VARCHAR2(100),

  INVOICE_NUM           VARCHAR2(200),

  INVOICE_LINE_NUM      VARCHAR2(10),

  CURRENCY              VARCHAR2(20),

BILL_TO_CUSTOMER_NAME VARCHAR2(1000),

  VAT_CODE              VARCHAR2(100),

  VAT_NUMBER            VARCHAR2(100),

  VAT_DATE              DATE,

  CREATION_DATE DATE,

  USER_NAME VARCHAR2(100),

  RESPO_NAME VARCHAR2(100),

  STATUS VARCHAR2(5)

)

   ---Check Table name in Fnd_Tables 

          select * from fnd_tables where table_name='XXST_GOLD_IMPORT_INT_BNE_T';

   ---- Delete table from fnd_tables

       EXEC AD_DD.delete_table('BNE','XXST_GOLD_IMPORT_INT_BNE_T')

   ---Register Table name 

   EXEC AD_DD.REGISTER_TABLE ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','T');

   --- Register Column names in Table

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','OPERATING_UNIT',1,'VARCHAR2',100,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','SOURCE',2,'VARCHAR2',100,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','INVOICE_NUM',3,'VARCHAR2',200,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','INVOICE_LINE_NUM',4,'VARCHAR2',10,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','CURRENCY',5,'VARCHAR2',20,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','BILL_TO_CUSTOMER_NAME',6,'VARCHAR2',1000,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','VAT_CODE',7,'VARCHAR2',100,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','VAT_NUMBER',8,'VARCHAR2',100,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','VAT_DATE',9,'DATE',9,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','CREATION_DATE',10,'DATE',9,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','USER_NAME',11,'VARCHAR2',100,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','RESPO_NAME',12,'VARCHAR2',100 ,'Y','N');

EXEC AD_DD.REGISTER_COLUMN ('BNE','XXST_GOLD_IMPORT_INT_BNE_T','STATUS',13,'VARCHAR2',5 ,'Y','N');

            

Interview

Business Email : Email Format

 

                Business email is very important while you are working in professional world. Today I am going to talk about email format. how can we create very professional email. you always keep in mind, you have need to follow 3W rules and some other sequences while writing any business email. 

             Let us talk about 3W  rule what is 3W rule, i am going break this 3w rule here

•  Who    --- whom you send email
• what action -- what action are you taking while send mail
• when   -- when are you come up with result, you have need to share certain deadline with receiver.

           what should common format follow while sending any type of professional email

          I am sharing exact sequences here

•     Friendly open
•     Reason for your mail
•     Attachment
•     Offering help
•     Closing line

Few rules you always need to follow 

1- Always re-read your email before send mail

2-     

Sample e-mail

 Hello Linda,
I hope you are doing well                --- friendly open

I am writing  to you with regards to reporting issue, As you requested , I have updated changes in report,                                                                                                      --Reason for your mail
I have enclosed attachment with my comments.-                                  -- attachment
If you have any further questions, Please let me know.  -- offering help
Looking forward you suggestion or feedback on report 

====================================================================

             

 

Business English: Wh Questions

 

    

question word	function	Business English Sentences
what	asking for information about something	What is your name? What is current project status?
	asking for repetition or confirmation	What? I can't hear you. You did what?
what...for	asking for a reason, asking why	What did you do that for? What did you do to fix this issue?
when	asking about time	When did he leave? When are you plan migrate the change into prod?
where	asking in or at what place or position	Where do they live? Where are store project documents?
which	asking about choice	Which colour do you want? Which type project do you want to work?
who	asking what or which person or people (subject)	Who opened the door? Who has made change for this invoice?
whom	asking what or which person or people (object)	Whom did you see?
whose	asking about ownership	Whose are these keys? Whose turn is it?
why	asking for reason, asking what...for	Why do you say that? Why are you make change in test instances?
why don't	making a suggestion	Why don't I help you? Why don’t work with your teammate?
how	asking about manner	How does this work? How does this work?
	asking about condition or quality	How was your exam?
how + adj/adv	asking about extent or degree	see examples below
how far	distance	How far is Pattaya from Bangkok?
how long	length (time or space)	How long will it take? How long will it take time to execute this program?
how many	quantity (countable)	How many cars are there? How many
how much	quantity (uncountable)	How much money do you have? How much ticket do you have now?
how old	age	How old are you?
how come (informal)	asking for reason, asking why	How come I can't see her? How come I can’t see this invoice in instance?

Business Meeting

SQL: Group By, Rollup, cube, grouping

 

   Grouping is used to identify count of distinct values in specific object. we have been applying grouping on  columns of table.

    Syntax 

•     Group by  <col_name1>, <col name2>
•     Rollup (<col_name1>, <col name2>)
•     Cube ((<col_name1>, <col name2>)
•     Grouping (<col name1>)

     We use group by to identify count or sum against distinct values of columns in table

      select col1 , col2, count(1) from table_t group by col1, col2.

here it is identified distinct value between col1 & col2 and display no of rows

Rollup   --- this function used to grouping in next level

     n --- No of columns

if we are doing rollup between 2 columns then you have need to do rollup next 3 level (n+1) level.

 

 First level --- It shows normal goruping

 2nd level   --- Grouping at distinct value of first columns

 3rd level    --  Grand total 

Cube   ---- This function used to grouping in next level

   n --- column numbers

( N*2)

so we have calculate level is 4

first level --- Shows normal grouping

2nd level -- Grouping at distinct value of first column

3rd level  -- Grouping at distinct value  of 2nd column

4th level  -- Grouping ( Grand Total)

==============================================

Testing script

create table group_test_t

( col1 number,

  col2 number)

 --- here my table has created

 -- I am going to understand grouping , gorup by ,rollup and cube function here

    --col1 has 2 distinct values and col2 has 3 distinct value

 ---step 2-- Insert data into table

   insert into group_test_t (col1,col2) values (1,1);

   insert into group_test_t (col1,col2) values (2,2);

   insert into group_test_t (col1,col2) values (2,3);

   ---step3 --- test group by function using count function now I am going find for 2 col 

   select col1,col2 ,count(1) as nu_rows from group_test_t group by col1,col2 order by 1

   --rollup always as n+1 level suppose there 2 col then leve would 3 level

   select col1,col2 ,sum(col1) as nu_rows from group_test_t group by rollup(col1,col2)  

      level 1 -- normal grouping 

      level 2-- grouping for each distict value in col1

      level 3 --- total grouping 

      col1  has value 1,  2

      col2 have value 1,2 ,3

      grouping normal 1,1 ==> 

                      2,2

                      2,3 

       level 2 grouping at distinct col1 

       1      ===> 1

       2 (2 times) ==> 2+2 =4

       level 3 --->

       1,2,3

              ==> level 2 grouping as 4 + 1 =5

              

   Now understand cube where level calculate as n*2

     select col1,col2 ,sum(col1) as nu_rows from group_test_t group by cube(col1,col2)              

       level 1-- Noraml Goruping

       level 2 --> grouping for distict value for col1

       level 3 --> grouping for distict value for col2

       level 4 --> total sub group.  

               

Unix

Oracle Workflow : Oracle Workflow Introduction

  Oracle workflow is complete workflow management system that facilities business process re engineering  Oracle workflow automatically process information and route from one place to other according business rules, you can easily change to any person inside or outside enterprise.

 Components of oracle workflow

1. Workflow Builder
2. Workflow Definition Loader
3. Workflow Notification system
4. Workflow engine.
5. workflow monitor.
6. Workflow Directory Services.

Oracle Workflow builder is graphical tool which help to design oracle workflow

Workflow Definition Loader is utility which help download and upload workflow definition between database and flat files.

Workflow notification  system handle send and receive response notification 

Workflow engine embedded in E-business Suite and it helps to monitors workflow status and coordinates  routing activities of process. 

        Workflow 

1. Attributes             =>  It is variable of oracle workflow
2. Process               ==>  Business process is combination activities
3. Notifications         =>  Define notification
4. functions              ==> PL\SQL functions
5. events                  ==>  Trigger
6. messages             ==> Define message for notifications
7. Lookup types        ==>  It is used display button in notification and list of values

  What are the item type attributes available in workflow?

          -Text

          -Number

          -Date

          -Lookup 

          -Form

          -URL

          -Document

          -Role

          -Attribute

        Oracle Workflow builder access level should be 100 for customization any workflow 

             Version 2.6.3.5

Access  level  : 100

Check box  which allowed modification of customized objects

I will explain about check box and access level  in next chapter.

          

PLSQL

AME:

XML Publisher

Oracle Web-adi Registration

      Oracle Web-adi has used to import data from 

    Step 1-- Please assign two responsibilities 

                              1. Desktop Integration Manager

    Step 2 --- Click on Integrator 

                  click on Display on document page -- you can able to see this in document 

          XXST_GOLD_IMPORT -- custom oracle function which has used to link the web-adi to responsibility

             

Step 2

         Step 3 ---  PLSQL based interface so enter plsql procedure name 

      ---SELECT RESPONSIBILITY_NAME  FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_ID = $ENV$.RESPID

      --select user_name from fnd_user where user_id=$env$.userid

   ---CUSTOM GOLDEN TAX  WEB ADI

           you can define multiple types of interface variables

  

            1-Environment Variable   --- ex sysdate

            2. SQL Query -- example --select user_name from fnd_user where user_id=$env$.userid

            3. Constant --- Example "Custom"

            4. Profile option

            5. lookup

            6.. Null --- default type as parameters of procedure.

FNDLOAD- Oracle Table

 

     Oracle Apps Table Migration using XDF Utility  

                            Oracle E Business Suite provides utilities like FNDLOAD,WFLOAD to migrate Application Objects from one instance to another.Similarly it provides XDF utility to migrate Database Objects.XDF utility is a Java class ,but a wrapped version of the it is available to be executed as Perl script too.Let us take an example to understand the utility in a better manner.Firstly we will create a custom table in a custom schema ,then generate the XDF file ,and finally apply the script to recreate the objects.

Requirement

                    Create XDF file for Table STRUCTURE

Input Data 

 Table names 

              BNE.XXST_GOLD_TAX_AUDIT_T

               BNE.XXST_GOLD_IMPORT_INT_BNE_T

Please follow below steps to genrate XDF file and upload into other instances

--#Step1

--  Create the initial table definition in your development database

create table BNE.XXST_GOLD_TAX_AUDIT_T
(
  OPERATING_UNIT VARCHAR2(100),
  INVOICE_NUM    VARCHAR2(200),
  VAT_CODE       VARCHAR2(100),
  VAT_NUMBER     VARCHAR2(100),
  VAT_DATE       DATE,
  USER_NAME      VARCHAR2(30),
  RESP_NAME      VARCHAR2(100),
  CREATION_DATE  DATE
)

create table BNE.XXST_GOLD_IMPORT_INT_BNE_T
(
  OPERATING_UNIT        VARCHAR2(100),
  SOURCE                VARCHAR2(100),
  INVOICE_NUM           VARCHAR2(200),
  INVOICE_LINE_NUM      VARCHAR2(10),
  CURRENCY              VARCHAR2(20),
  BILL_TO_CUSTOMER_NAME VARCHAR2(1000),
  VAT_CODE              VARCHAR2(100),
  VAT_NUMBER            VARCHAR2(100),
  VAT_DATE              DATE,
  CREATION_DATE         DATE,
  USER_NAME             VARCHAR2(100),
  RESPO_NAME            VARCHAR2(100),
  STATUS                VARCHAR2(5)
)

#Step2   Upgrade the table for Online Patching using the AD_ZD_TABLE.UPGRADE procedure

   ---This will generate an Editioning View (EV) for the table and then create an APPS synonym that points to the Editioning View.

     login in PLSQL and run below command

•   exec ad_zd_table.upgrade('<app_name>', '<Table_name>');
•   exec ad_zd_table.upgrade('BNE', 'XXST_GOLD_TAX_AUDIT_T');
•   exec ad_zd_table.upgrade('BNE', 'XXST_GOLD_IMPORT_INT_BNE_T'); 

#Step3

               Identify mapping between Apps synonym and table 

•           cd $AD_TOP/sql
• sqlplus --connect with DB.
• --Run sql 
• @ADZDSHOWEV.sql XXST_GOLD_TAX_AUDIT_T
•     @ADZDSHOWEV.sql XXST_GOLD_IMPORT_INT_BNE_T

---you can run below command.

1. $AD_TOP/sql/ADZDSHOWEV.sql XXST_GOLD_TAX_AUDIT_T
2.        $AD_TOP/sql/ADZDSHOWEV.sql XXST_GOLD_IMPORT_INT_BNE_T

#Step 4 --Export table definition from Database and generate XDF files
          

 Extract the table definition from your development database using the xdfgen.pl utility.   
 cd   $FND_TOP/bin/

•  perl xdfgen.pl apps/Stiapps4dev XXST_GOLD_TAX_AUDIT_T
•  perl xdfgen.pl apps/Stiapps4dev XXST_GOLD_IMPORT_INT_BNE_T

  # Step 5 -- Upload XDF into other instance Database

   perl xdfgen.pl <apps_user>/<apps_password> XYZ_USER_SERVICE

===========================================================

   ---Note if you are alterting table then run below command to update existing EV

   Regenerate the editioning view using AD_ZD_TABLE.PATCH

      exec ad_zd_table.patch('APPLSYS', 'XXST_GOLD_TAX_AUDIT_T')