NALAM Oracle Apps Technical : Oracle Apps Technical knowledge

Oracle Apps Technical

Topics:

SQL

PLSQL

Forms (.fmb – forms module binary)

Reports (.rdf – report definition file)

Sql*loader (.ctl – control file)

Shell script (.prog – program file)

Workflow (.wft – work flow type)

Libraries (.pll – program link library)

Sequel (.sql)

Roles:

DBA Role

Functional Role

Technical Consultant

Technology:

Oracle apps R12/ Oracle Cloud/Fusion

Induction:

Receive Laptop:

Softwares:

Outlook/Web mail

Micro Soft Teams

SQL Developer/Toad

Forms Builder

Report Builder

Workflow Builder

Putty

Winscp/Filezilla

Communication Methods:

Micro Soft Teams

ZOOM

Business skype

Employee Hierarchy or Reporting Hierarchy:

Developer or Fresher or SrDevelopers

Team lead/Technical Lead

Project Manager

Delivery Manager

Group Head or Practice head

CEO

Onsite/Off Shore:

Capgemini (India) – GE Aviation (USA)

Capgemini (India) – Aurobindo

Onsite: Client Location

Offshore: Our payroll company (Capgemini, Birla soft, Infosys…………………..)

Onsite COORDINATOR: He is the bridge between onsite and offshore team

How to get Oracle database version:

SELECT * FROM V$VERSION;

How to get Oracle Apps Version:

SELECT release_name FROM FND_PRODUCT_GROUPS;

FND – Foundation (AOL)

EBS Version or Oracle Apps Versions:

R12

=====

12.2.10

12.2.9

12.2.8

12.2.7

12.2.4

12.1.3

12.1.1

12.0.0 --May/Jun 2006

11i

====

11.5.10.2

11.5.10

11.5.9

11.5.7

ERP/EBS

Enterprise resource Planning

4m’s

Men, Machine, Material and Money

If u reduces Inputà Will get more o/p or Productivity

Copper Rod:

Agent

We are creating RFQ and send to companies/Shops

Quotation

Accounts:

1980—MRP (Material Requirement Planning)

MRP11

ERP

ERP Vendors:

Oracle Apps (US)

SAP (Germany)

People Soft

Siebel

JD EDWARD

SAAS

MS Dynamics

TALLY

Schema: Collection of data base objects which related to specific functionality

Ex: PO, AP, AR, GL, CM, INV, OM

SCOTT/TIGER@ORACLE

APPS/APPS@VIS

APPS/APPS@DEV

APPS/APPS@PROD

PO/PO@PROD

EMP

How to connect one schema object to another schema object: GRANT

Grant to AP schema

SELECT * FROM PO.EMP;

Database: Collection schemas.

Ex: DEV, TEST, UAT, PROD

DEV

PO.EMP

TEST

D_T

D_P T_P

PROD

How to connect one data base object to another data base object: db link

SELECT * FROM PO.EMP@D_T

DB Link: It is the link between one database to another data base. It will create by DBA.

Types of Instances or Software:

Vision

PROD or Blank instance (DEV, TEST, UAT, PROD)

DEV: Full permissions

TEST, UAT, PROD: Read Only

Modules:

FINANCE

SCM (Supply chain management)

CRM (Customer relationship management)

HRMS (Human resource management) – HCM – HUMAN CAPITAL MANAGEMENT

PA (Project accounting)

MFG (Manufacturing)

FINANCE:

AP: Account payables (CR)

AR: Account Receivables (DR)

GL: General Ledger

CM: Cash Management

FA: Fixed Asset

PEN

LAPTOP: 70000, 50000, 30000, 20000, 5000, 0 (SCRAP)

LAND:

COMPANY:

REVENUE

ASSET

EXPENSE

ASSET

SCM (Supply chain Management)

INV: Inventory

PO: Purchase Orders

OM: Order management

P2P: Procedure 2 Payment (Customer)

O2C: Order 2 Cash (Supplier)

MFG:

INV

BOM: Bill of materials

WIP: Work in process or work in Progress

HRMS:

Core HRMS

T&L (Time &Labor)

Payroll

GAP Analysis:

The functional consultant will gather client business and compare with Oracle apps software and identify the gaps.

The gaps will be fulfilled by Technical Consultant (Code: Forms, reports, packages, triggers)

IRCTC: Oracle apps

Two types of Bookings: Agent Booking, Direct Booking

Direct Booking: 5000à IRCTC (5000)

Agent Booking: 5000à IRCTC (4900) + 100 (Agent)

X: 100

Y: 100

TABLES, PACKAGE, FORMS

Roles and responsibilities:

DBA:

Install oracle apps software

Apply patches (support.oracle.com)

Cloning(Hot Backup, cold Backup)

Data backup

Create users, Directories, Permissions

Functional Role

Identify gaps and prepare MD050 document (Functional design document)

Functional setups

Prepare BR100 document (Setup document)

BR: Business requirement

MD: Module

Technical Role:

Develop RICEW components

R Reports

I Interface

C Conversion

E Extension

W Workflow development

Prepare MD070, MD120, TE020

MD070 – Technical Design Document

MD120 – Migration document

TE020 – Unit test cases document

Write adhoc SQL queries

AIM Methodology (Application Implementation methodology):

Identify GAP

Prepare MD050 (Functional design document)

Prepare MD070 (Technical design document)

Send MD070 to technical lead/PM/Team lead

Start develop code in DEV instance

Prepare Unit test cases (TE020) and do unit testing

Prepare MD120(Code migration document or readme.txt) for code migrations

Send mail to Users/Onsite Guys

UAT will start

Code will migrate to PROD (GOLIVE)

Types of test cases:

TE020 (Unit test case documents) –UIT --Developers

TE040 (User acceptance testing) –UAT -- End User/Business Users

DEV

TEST

PROD

UIT UAT

Developers End User/Clients

TE020 TE040

Data flow in oracle apps:

AUROBINDO:

CHEMICALS

DRUGS

INV

BOM & WIP

20000—CR

30000—DR

P2P: Company & Supplier

O2C: Company & Customer

Cash management: To clear (Reconcile) oracle apps transactions (AP Payment, AR Receipt, HRMS Payroll) against bank statement.

Types of projects:

Implementation: From scratch we are implementing or Brans new implementation

Support: After implementation the support project will start to fix production issues/Bugs

Upgradation: To upgrade from lower version to Higher version (11i à R12)

Rollout: Add or rollout software to new countries

Infosys: GE

Infosy: 60 usd

Oracle: 120 usd

Apps associate: 40

15 usd

Support Project:

Types of tickets:

New development

Enhancement

Bug/Issue

Priority of ticket:

Critical

High

Medium

Low priority

SLA (Service level agreement)

Critical: (1 – 2 Hrs)

High (2 to 8 Hrs)

Med: 5 to 20 Hrs)

1 CRITICAL -- NEW DEVELOPMENT

1 HIGH -- BUG

Three tier architecture:

Front end tier/Application/User

Middle tier/Linux File system

DB Tier/Back end tier

DB tier/Back end tier: We will use sql developer/TOAD to connect to db tier.

It’s a collection of data base objects (TABLE, PACKAGE, PROCEDURE, SYNONYM, VIEW, TRIGGER…..)

Middle tier: It’s a collection of files (.fmb, .rdf, .ctl, .sql, .prog, .pll)

We will use ftp (file transfer protocol) tools transfer files from window to Linux middle tier and vicevrsa.

ftp tools:

=======

Winscp

Filezilla

Toad

Front end tier: It’s a collect of user and responsibilities

We will use i.e.or Mozilla to connect to front end tier.

Middle tier:

Oracle

Ud1

appl comn ora db data

PO AP AR GL CM INV ONT ……200+ PROD TOPS XXGE (Custom top)

| |

12..0.0 –Version (R12) 12.0.0

| |

Forms reports sql binjava admin help forms reporsqlbin

| | | |

US D E US D E .sql .ctl

.fmb .fmb .fmb .rdf .rdf .rdf .prog

.fmx .fmx .fmx

Seeded software (Oracle given)

PROD TOP: The directory which given by oracle for seeded software

EX: PO, AP, AR, GL, CM, INV, ONT……200+

Custom TOP: The directory which created by DBA for new development.

EX: XXGE

APXVDVSR.rdfà AP/12.0.0/reports/US

XXPOREPORT.rdfà CUSTOM TOP/12.0.0/reports/US/

Front end tier:

USER

PO RESP AP RESP AR RESP GL RESP OM RESP APPLICATION DEVELOPER SYSTEM ADMINISTRATOR

Menu request Group Data group (Standard)

| |

Collection of forms collection of reports/ Concurrent programs

PO MENU

-----------

QUOTATION

REQUISITION

INVOICE

AP MENU

--------

INVOICE

PAYMENT

AOL (Application Object Library –FND--FOUNDATION)

====

APPLICATION DEVELOPER

SYSTEM ADMINISTRATOR

Application (Module): It’s a collection of specific business operations.

EX: PO, AP, AR, GL, CM, ONT

Responsibility: it’s a collection miscellaneous business operations, using Responsibility will connect to multiple applications.

EX: PO RESP, PO SUPER USER RESP, OM RESP……..

FINANCE - CLERK

How to connect to Client Network

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

1.VPN(Virtual private Network) Eg; Zscaler,Cisco

2.citrix

3.RDP(Remote Desktop)

DAY1:

Induction

HR will share the PM mail id and mobile number.

LAPTOP

VPN Credentials

DB/Middle Tier/Application Tier Credentials

How to connect to Application

A1) Login to oracle application (Front end tier) with default user “OPERATIONS” and password “welcome”

A2) Create new user and add 2 responsibilities (Application Developer and System Administrator) and logout

A3) Log into oracle apps with new user/password and reset password

A4) Check user exists or not

F11

CTRL F11

FN F11

FN CTRL F11

Click on view--> query by Example--Enter

Click on view--> query by Example—Run

SELECT * from FND_USER where USER_NAME = 'RAMA';

SELECT * FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_NAME = 'PO US RESP RAMA';

SELECT * FROM FND_REQUEST_GROUPS WHERE REQUEST_GROUP_NAME = 'PO US GROUP RAMA';

WHO Columns

========== =

To track who created and who updated and when created and when recently updated

CREATED_BY -- NUMBER

CREATION_DATE -- DATE

LAST_UPDATED_BY -- NUMBER

LAST_UPDATE_DATE -- DATE

LAST_UPDATE_LOGIN -- NUMBER

How to get user name and who created

select FU.USER_NAME,

fum.USER_NAMEcreate_by

from FND_USER FU,

FND_USER FUM

wherefu.USER_NAME = 'RAMA2'

AND fu.created_by = fum.user_id;

A1) Create request group

A2) Create responsibility

A3) Assign resp to user

A4) Get table name for users, resp, request group

A5) Who column

SQL * Plus Development and Registration:

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

1) Develop .sql file as per client requirement

2) Transfer .sql file from local machine to server (Custom top/12.0.0/sql)

3) Define executable (EXECUTION METHODàsql * plus) --12 Methods

4) Define concurrent program (Report)

5) Attach concurrent program to Request Group

6) Click on switch responsibility button and select responsibility ”PO US RESP RAMA”

7) Submit a concurrent program through SRS window

SRS: STANDARD REQUEST SUBMISSION WINDOW

Oracle will generate request id and generate two file (Output file, Log file)

PLSQL Stored Procedure

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

1) Develop plsql stored procedure as per client requirement

2) Compile plsql stored procedure (F5)

3) Define executable (EMàPlsql Stored procedure)

4) Define concurrent program

5) Attach concurrent program to request group

6) Click on switch responsibility button

7) Submit a concurrent program using SRS window

Standards while developing procedure

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

1) Need to take two mandatory parameter (ERRBUF and retcode)

2) Use FND_FILE.PUT_LINE instead of DBMS_OUTPUT.PUT_LINE

A) ERRBUF: It is an out parameter and Data type is VARCHAR2 and mandatory parameter. It is used to capture error messages and print in log file

B) RETCODE: It is an out parameter and data type is NUMBER. It is used to getstatus of concurrent program or set status of concurrent program.

RETCODE will return three values

RETCODE Value	Phase	Status	Color
0	Completed	Normal (Success)	Grey
1	Completed	Warning	Yellow
2	Completed	Error	RED

C) Display messages in output file

a. FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’mesagges…….’);

D) Display messages in log file

a. FND_FILE.PUT_LINE(FND_FILE.LOG,’mesagges…….’);

Emp report:

Deptno:40

WARNING -- YELLOW COLOR--- NO DATA FOUND

create or replace procedure XXIM_PROC_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Message for output file........');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Message for log file........');

END;

TESTCASE 1)

create or replace procedure XXIM_PROC_RAMA IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Message for output file........');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Message for log file........');

END;

TESTCASE 2)

create or replace procedure XXIM_PROC_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Message for output file........');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Message for log file........');

RETCODE:= 1;

END;

TESTCASE 3)

create or replace procedure XXIM_PROC_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Message for output file........');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Message for log file........');

RETCODE:= 2;

END;

TESTCASE4)

create or replace procedure XXIM_PROC_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Message for output file........');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Message for log file........');

ERRBUF:= 'DUMMY MESSAGE';

END;

Oracle Reports (.RDF):

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

1) Develop .rdf as per client requirement

2) Transfer .rdf from local machine to server (Custom top/12.0.0/report/US)

3) Define executable (EMà Oracle Reports)

4) Define concurrent program

5) Attach concurrent program to Request group

6) Change Responsibility (PO US RESP RAMA)

7) Run concurrent program through SRS window

Standard for oracle reports

1) Create user parameter called “P_CONC_REQUEST_ID”

It is the bridge between oracle apps and report builder. To get REQUEST_ID

2) Call user exit in before Report trigger

SRW.USER_EXIT(‘FND SRWINIT’);

It is used to initialize the profile values

3) Call user exit in After report trigger

SRW.USER_EXIT(‘FND SRWEXIT’);

It is used to deallocate the memory which occupied by profiles

SRW: Sequel report writer.

Sequence of Report builder triggers:

1) Before parameter form

2) After parameter form

3) Before report

4) Between pages

5) After report

Enamesalhiredate

SCOTT 10,000.00 10-JAN-2021,. DD-MON-YYYY

SCOTT 10.000,00 01/10/2021., MM/DD/YYYY

8300+

Propery Inspector (F4)

Object Navigator (F5)

Report Editor (F7)

PL/SQL Editor (F11)

ctrl + Shift + K

MULTI ORG(MO)

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

Under single implementation to maintain multiple operating Units (Countries)

IM (US, CANDA, IND, GERMANY…….)

PO FORM:

US: USD

IND: INR

GERMANY: EUR

HRFV_BUSINESS_GROUPS --HRMS--Business Group–It secures human information

HR_LEGAL_ENTITIES--HRMS--Legal entity– Tax reporting entity to the Govt

GL_LEDGERS--GL--Ledger—Collection of 4c’s(Currency, Calendar, COA, subledger A/C Method)

HR_OPERATING_UNITS--PO, AP, OM, AR, CM--Operating Unit–Main branch or Division or sales office

Purchasing

Payment

Orders

Cash Receipt

Cash Management (Reconciliation)

MTL_PARAMETERS----INV--Inventory organization/Mfg Plane/Warehouse–To track Inventory information

MTL_SECONDARY_INVENTORIES--INV--Subinventory or store room—Part of warehouse to store material

MTL_ITEM_LOCATIONS--INV--Stock Locators--Partition of store room for easy identification

MTL_SYSTEM_ITEMS_B--INV—Items

MTL_SYSTEM_ITEMS_TL

MTL—Material

_B --Base language (US)

_TL – Translated Language

Amazon:

India: Paper

Germany: Papier

Spanish :Papel

MTL_SYSTEM_ITEMS_B

PAPER

MTL_SYSTEM_ITEMS_TL
US PAPER

DE PAPIER

E PAPEL

Data migration:

Legacy System

Java

SFDC

.NET, SAP, SAAS

EBS (Oracle apps)

Inbound Interface

Outbound Interface

Legacy System: Any software other than core technology.

Ex: Java, Sales force (SFDC), .net, SAAS, people soft

Inbound Interface: To transfer data from legacy to oracle applications.

Input	Technology	O/p
.txt, .csv, .dat	*SQL Loader**	TABLE
.txt, .csv, .dat	UTL_FILE	TABLE
.txt, .csv, .dat	External Table	TABLE
TABLE	@db link	TABLE

Outbound Interface: To transfer data from oracle applications to legacy.

Input	Technology	O/p
TABLE	UTL_FILE	.txt, .csv, .dat
TABLE	Spool command	.txt, .csv, .dat
TABLE	@db link	TABLE

UTL_FILE for outbound Interface

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

1) Get utl_file directory path (SELECT Value from v$parameter where name = 'utl_file_dir';)

2) Develop pl/sql stored procedure

3) Compile pl/sql stored procedure

4) Define executable (EMàpl/sql stored procedure)

5) Define concurrent program

6) Attach concurrent program to request group

7) Change responsibility (PO US RESP)

8) Run concurrent program through SRS window

9) Open winscp and check file

UTL_FILE Modes

1) R – READ

2) W – WRITE

3) A – APPEND

4) RB – READ BINARY

5) WB – WRITE BINARY

6) AB – APPNED BINARY

create or replace procedure xxim_user_data_rama(errbuf out varchar2,

RETCODE OUT NUMBER) IS

l_file_type UTL_FILE.FILE_TYPE;

BEGIN

l_file_type := UTL_FILE.FOPEN('/usr/tmp','IM_USER_DATA_RAMU.csv','W');

FOR REC IN (SELECT USER_NAME,USER_ID FROM FND_USER)

LOOP

UTL_FILE.PUT_LINE(l_file_type,rec.user_name||','||rec.user_id);

END LOOP;

UTL_FILE.FCLOSE(l_file_type);

END;

SQL * Loader

==========

1) Will get data file from client

2) Transfer file from local machine server (PO/12.0.0/data or in )

3) Create table in custom schema

4) Create synonym in APPS schema

5) Develop .ctl file

6) Transfer .ctl file from local machine to server (PO/12.0.0/bin)

7) Define executable (EMèSQL * Loader)

8) Define concurrent program

9) Attach concurrent program to request group

10) Change responsibility “PO US RESP RAMA”

11) Run concurrent program through SRS window

SUPPLIER NAME,SITE,START_DATE,

amazon,HYD,01-JAN-2021,

TCS,CHENNAI,10-DEC-2021,

“SONATA,SOFTWAREindia private limited”,HYD,02-01-2022,

“Am,azon”,HYD,01-JAN-2021,

CREATE TABLE PO.XXIM_SUPPLIERS_STG_RAMA(

SUPPLIER_NAME VARCHAR2(20),

SITE VARCHAR2(10),

START_DATE DATE);

CREATE OR REPLACE SYNONYM XXIM_SUPPLIERS_STG_RAMA FOR PO.XXIM_SUPPLIERS_STG_RAMA;

Options(skip=1,load=20)

LOAD DATA

INFILE “/ORA/db/po/12.0.0/data/SUPPLIERS_DATA.txt”

append into table XXIM_SUPPLIERS_STG

whensite”HYD”

Fields terminated by “,” --double quote||coma||double quote

Optionally enclosed by ‘”’ --single quote||double quote||single quote

(supplier_name “upper(:supplier_name)” ,

Site filler,

Start_date)

1) Modes in sql * Loader

a. INSERT: for insert option the table must be empty

b. REPLACE: It will delete old data from table and load new records

c. TRUNCATE: Same as replace option but implicit commit will fire

d. APPEND: It will add new records to the existing records on table

2) Options

a. Skip: to skip N number of records

b. Load: To load N number of records

3) Optionally enclosed by: To skip delimiter if it is exists in column value

4) Filler: Skip column values

5) How to call functions in control file: It should enclose with double quotes and specify bind parameter “:” before column name

6) We can’t call procedures in control file

7) File types:

a. Discard file (.dis): Whenever when condition is not satisfied then record will insert into discard file

b. Bad file (.bad): It will generate automatically whenever file format is not correct

(Wrong date format mask, insufficient data size, mismatching data type, Violating constrains)

c. Log file (.log): To get error messages, how many records load, how many record moved bad file, How many records moved discard file

REPLACE:

2,00,000à RBS

50,000à 50,000

TRUNCATE:

2,00,000à DELETE

50,000à

Value sets:

=======

List of values. The user should enter correct values.

We can use value sets in concurrent programs (Parameters)and Flex fields

There are two methods to create value sets

a) Table (Sql query)

b) Static

Printer

Types of value sets: 8

1) NONE

2) INDEPENDENT

3) DEPENDENT

4) TRANSLATABLE INDEPENDENT

5) TRNSLATABLE DEPENDENT

6) SPECIAL

7) PAIR

8) TABLE

NONE: It is based on neither static nor sql query, No list of values at run time. Free text, value set, But user should follow format (Number, Date, and Char)

Date

Report: Invoice date

FND_STANDARD_DATE --NONE VALUE SET FOR DATE PARAMETER

INDEPENDENT: It is based on static method, These values will not dependent on prior selection.

DEPENDENT: It is based on static method and these values will dependent on independent value set or prior selection.

We can’t create dependent value set without creating independent value set.

Country: India, USA, GERMANY

City: Hyderabad, delhi, Chennai…..

TRANSLATABLE INDEPENDENT and TRANSLATABLE DEPENDNET.Same as independent and dependent value sets But translatable field will be enabled to enter translated value.

Report:

Send Email: Yes/No

Ja/Nein

TABLE: It’s based on table (SQL query). We need to enter table name, column name, type, size, where/order by and additional columns to create table value set.

Special and pair(For validation purpose and flex field purpose)

TABLE VALUE SET:

create or replace procedure XXIM_GET_EMP_DATA_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER,

P_EMPNO IN NUMBER) IS

BEGIN

FOR REC IN (SELECT * FROM EMP WHERE EMPNO = P_EMPNO)

LOOP

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,REC.ENAME||','||REC.SAL||','||REC.DEPTNO);

END LOOP;

END;

INDEPENDENT and DEPENDENT

create or replace procedure XXIM_COUNTRY_CITY_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER,

P_COUNTRY IN VARCHAR2,

P_CITY IN VARCHAR2) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Country:'||P_COUNTRY);

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'City:'||P_CITY);

END;

*****MAX WE CAN ADD 100 PARAMETERS TO THE CONCURRENT PROGRAM

TRANSLATABLE DEPENDNET

create or replace procedure XXIM_INVOICE_REPORT_RAMA(ERRBUF OUT varchar2,

RETCODE OUT NUMBER,

P_EMAIL IN VARCHAR2) IS

BEGIN

IF P_EMAIL = 'YES' THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Email has been sent');

ELSE

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Invoice sent to printer');

END IF;

END;

Value set table:

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

SELECT * FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME = 'XXIM_COUNTRY_RAMA';

SELECT * FROM FND_FLEX_VALUES WHERE FLEX_VALUE_SET_ID = 1026308;

Table

$FLEX$

How to link one table value set to another table value set

create or replace procedure XXIM_DEPT_ENAME_RAMA(ERRBUF OUT varchar2,

RETCODE OUT number,

P_DEPTNO in number,

P_ENAME IN VARCHAR2) IS

BEGIN

FOR REC IN (SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO AND ENAME = P_ENAME)

LOOP

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,REC.DEPTNO||','||REC.ENAME||','||REC.SAL);

END LOOP;

END;

WHERE DEPTNO = :$FLEX$.XXIM_DEPTNO_RAMA

FLEX (UPPER CASE)

XXIM_DEPTNO_RAMAà PRIOR VALUE SET NAME (Master value set name without any space while creating value set)

Shell script:

1) Develop .prog file as per client requirement

2) Transfer .prog file from local machine to server (PO/12.0.0/bin)

3) Open putty tool and give full permissions to the file using the command chmod 777

4) Remove special characters(M) by using the command dos2unix

5) Generate link (soft link) file using the command “ln”

6) Define executable (EM—HOST)

7) Define concurrent program

8) Attach cp to rg

9) Change resp

10) Submit program through SRS window

Default parameters in shell script

$1—DB USERNAME/PWD

$2—ORACLE APPS USER ID

$3 – ORACLE APPS USER NAME

$4—REQUEST ID

Usages of shell script

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

1) Transfer files from one server to another server (ftp purpose)

2) To send emails (mailx)

3) To process multiple data files in sql * loader

4) Execute unix command (mkdir, cp, rm, ls)

cd – change directory

ls – list files or directories

pwd – print working directory / present working directory

cp – copy

mv – move

rm – remove files

mkdir – to create directory

echo – to print messages

R W X

PADMAJA --APPLDEV

JHANSI

TIRUPATHI --APPLTEST

chmod 7(4R_2W_1X) 7(4R_2W_1X) 7(4R_2W_1X)

D G O

Chmod 155

XXIM_CREATE_DR.prog

XXIM_CREATE_DR --soft link

cd /oraAS/oracle/VIS/apps/apps_st/appl/

ls *.env

. APPSVIS_glo.env

cd $PO_TOP/bin

pwd

chmod 777 XXIM_CREATE_DIR_R.prog

dos2unix XXIM_CREATE_DIR_R.prog

ln -s $FND_TOP/bin/fndcpesr XXIM_CREATE_DIR_R

PO --Purchase orders

Buyers

(Headers, Lines, Distributions)Requisitions (Internal, Purchase)

DELL RFQ (Request for quotations) (Bid, catalog, Standard)

IBM |

HP Quotations (Bid, catalog, Standard)

(Headers, Lines, Shipment and Distributions)PO (STANDARD, PLANNED, BLANKET, CONTRACT)

Receipt or acknowledgement (Direct, standard, inspection required)

LAPTOP

100*70,000*20-FEB-2022

DELL

====

100*72,000*20-FEB-2022

IBM

====

100*80,000*01-FEB-2022

====

100*68,000*20-MAR-2022

Requisition:

Internal: To transfer goods from one branch to another branch or one organization to another organization within the company.

Purchase: To but goods or services from supplier

RFQ Types:

Standard: One time of various components.

Bid: High price and low quantity

Catalog: Low price and High qty

===

STANDARD: One time of purchase of various components

PLANNED: It’s a long term agreement between you and your supplier. We need to create scheduled releases against planned po.

SCHEDULED RELEASES

01-FEB-2022 – 80

01-MAR-2022 – 90
01-APR-2022 – 80

BLANKET: It’s a specific time of agreement between you and your supplier. We need to create blanket releases against Blanket po. In blanket there is price breakup functionality.

10 * 70000

20 * 69000

50 * 68000

100 * 67000

CONTRACT: It is the high level agreement between you and your supplier. We need to create standard po against contract agreement.

Outsourcing:

Magna infotcà TCS

Receipt Types or receipt routing types:

Direct: Based on single locations (Store room)

Standard: Based on two locations (Locations + Store room)

Inspection required: It is based on three locations (Location + Inspection + Store room)

+ Return to vendor)

Employee (PER_ALL_PEOPLE_F)

Buyer (PO_AGENTS_V)

Assign Buyer to FND user (FND_USER)

ETRMS--ELETRONIC TECHNICAL REFERENCE MANUAL

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1 = '14337';

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID = 191171;

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID = 218379;

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 = '328' AND TYPE_LOOKUP_CODE = 'RFQ';

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID = 121309;

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID = 182238;

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 = '521' AND TYPE_LOOKUP_CODE = 'QUOTATION';

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID = 121309;

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID = 182238;

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 = '6073' AND TYPE_LOOKUP_CODE = 'STANDARD';

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID = 121311;

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID = 182240;

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID = 273386;

SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM = '23699';

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID = 4818908;

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_LINE_ID = 4809469;

Supplier: Headers: Supplier name, type (Food, Electrical, stationary, comp)

Site: A1, A2, A3, A4, CITY, STATE, COUNTY, COUNTRY

Site usage: Purchasing, Payment, RFQ

Contact details: Full name, email, phone number

Terms and conditions: Currency, Terms name (Immediate, 90 Days)

AP Invoice: Headers: Invoice num, Date, Amount, currency

Lines: Line type (Item, freight, TAX)

Distributions (A/C DISTRIBUTIONS) --Charge a/c, Liability

Payment schedule: Due date, Due date

Hold &Release

Payment: Checks: Check number, Check date, amount

Invoice details: Invoice num, Amount

Run “Create accounting program” to transfer data to GL module

1 – 20000

5 – 50000

Invoice types:

STANDARD

CREDIT MEMO or CREDIT NOTE

DEBIT MEMO

EMP EXPENSE

RECURRING

PREPAYMENT

TCS: MINDSPACE 3 YERS 36

1,00,000 O1-JAN-2022

2 * 10%

1,10,000

SELECT * FROM AP_SUPPLIERS WHERE VENDOR_NAME = 'BIGBASKET';

select * from AP_SUPPLIER_SITES_ALL where VENDOR_ID = 41169;

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM = 'BB1';

SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID = 218300;

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID = 218300;

select * from AP_PAYMENT_SCHEDULES_ALL where INVOICE_ID = 218300;

SELECT * FROM AP_CHECKS_ALL WHERE CHECK_NUMBER = '6404';

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID= 81530;

Inventory:

Mater inventory organization --MTL_PARAMETERS

Child inventory organization --MTL_PARAMETER

Sub inventory --MTL_SECONDARY_INVENTORIES

Stock Locators – MTL_ITEM_LOCATIONS

Items: Define item in master inventory organization

Assign item to child inventory organization

Assign item category

Assign template

MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_TL

MTL_ITEM_CATEGORIES

MTL_ITEM_REVISIONS

We can’t create item directly in child inventory organization

V1 – Master

ORGANIZATION CODE ORGANIZATION ID MASTER ORGANIZATION ID

V1 204 204

M1 207 204

M2 209 204

Master org : ORGANIZATION ID = MASTER ORGANIZATION ID

Child org: ORGANIZATION ID <> MASTER ORGANIZATION ID

Bolt

Nut

Stud

Tod

Hammer

Switch

Wire

Pen

Paper

Pad

Marker

Mouse

Keyboard

SELECT PLA.QUANMTITY * PLA.INTI_PROCE

FROM PO_LINES_ALL

WHERE ITEM IN (‘Pen’,

‘Paper’,

‘Pad’,

‘Marker’)

SELECT PLA.QUANMTITY * PLA.INTI_PROCE

FROM PO_LINES_ALL

WHERE category = ‘stationary’

Category:

Logical grouping of inventory items based on simile properties

Ex:

Stationary

Computer

Hard ware

Electric

Template:

Purchasing:

UOM -- Y

STOCKABLE

PURCHSE - Y

CUSTOMER ORDRES

RETURNABLE

Selling:

UOM

STOCKABLE -- y

PURCHSE

CUSTOMER ORDRES -- y

RETURNABLE -- y

BOLT:

SELECT * FROM MTL_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = 'FGI_R';

SELECT * FROM MTL_ITEM_LOCATIONS WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

SELECT * FROM MTL_ITEM_LOCATIONS_KFV WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = 'BOLT';

SELECT * FROM MTL_SYSTEM_ITEMS_TL WHERE INVENTORY_ITEM_ID = 5027190;

SELECT * FROM MTL_ITEM_CATEGORIES WHERE INVENTORY_ITEM_ID = 5027190;

SELECT * FROM MTL_ITEM_REVISIONS WHERE INVENTORY_ITEM_ID = 5027190;

_KFV -- KEY FLEX FIELD VIEW

Order Management:

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

Enter the sales order

Book the sales order

Release sales order (Pick release + Pick confirm)

Shipping

Run program “workflow background process” to create AR Invoice

Create cash receipt

Run program “create accounting” to transfer data to GL module

Types of sales order:

STANDARD

RMA (Return material authorization)

Internal so

Drop shipment

SELECT * FROM FND_APPLICATION_VL WHERE APPLICATION_NAME = 'Purchasing'

SALES ORDER TABLES

ENTER
-------

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 66432;

SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 200312;

BOOK

------

SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 200312; -- HEADER_ID

select * from WSH_DELIVERY_ASSIGNMENTS where DELIVERY_DETAIL_ID = 3972467; -- HEADER_ID

RELEASE SO

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

SELECT * FROM WSH_PICKING_BATCHES WHERE ORDER_HEADER_ID = 200312; --HEADER_ID

SELECT * FROM WSH_NEW_DELIVERIES WHERE DELIVERY_ID = 3781372; --WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID

SHIPPING

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

SELECT * FROM WSH_DELIVERY_LEGS WHERE DELIVERY_ID = 3781372;

SELECT * FROM WSH_TRIP_STOPS WHERE STOP_ID = 3178664; --PICK_UP_STOP_ID

SELECT * FROM WSH_TRIPS WHERE TRIP_ID = 3134350;

AR INVOICE

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

SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE INTERFACE_HEADER_ATTRIBUTE1 = '66432'; -- ORDER NUMBER

SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID = 763706;

SELECT * FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE CUSTOMER_TRX_ID = 763706;

SELECT * FROM AR_PAYMENT_SCHEDULES_ALL WHERE CUSTOMER_TRX_ID = 763706;

Flex field (Flexible field):

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

It is a combination of fields to store extra information or to store code combinations.

Types of flex fields:

DFF (Descriptive flex field)

KFF (Key flex field)

EMP

ENAME EMPNO SAL COMM DEPTNO ATTRIBUTE1 ATTRIBUTE2…..SEGMENT1 SEGMENT2….

FORM

REPORT

VIEW

DFF	KFF
Descriptive flex field	Key flex field
To store extra information	To store code combinations
Extra information will be stored on ATTRIBUTE COLUMNS EX: ATTRIBUTE1, ATTRIBUTE2…..	Code combinations will be stored in segment columns EX: SEGMENT1, SEGMENT2……30
ATTRIBUTE COLUMN DATA TYPE: VARCHAR2	SEGMENT COLUMN DATA TYPE VARCHAR2
DFF symbol: Box with closed brackets	KFF Symbol: List lamp
It will allow duplicate values	It will not allow duplicate values (Unique element)

Stock Locator (INV Module)

Honda:

ROW|RACK|BIN

1|1|BOLT

BAJAJ:

ROW|RACK|BIN|BIKE TYPE

A/c flex field (GL Module)

IBM

IBM INDIA|HYD|EMP EXP|IT|PEN ---50

SONATA

SSL|BLR|EMP EXP|SUMMER|IT|PEN|STATIONARY

SONATA SOFTWARE

LAPTOP --AMAZON--DELL

Profiles:

=======

It is an option to change way of application running dynamically.

PADMAJA: US

JHANSI: IND

KHAJA: GERMANY

Will set profiles at four levels

a) Site

b) Application

c) Responsibility

d) User

What is the navigation to check profile values: 8300

Application Developerà Profiles

Ctrl + F11

The functional consultant will set profiles

The technical consultant will get profiles in back end (Packages, forms, reports, shell script……)

How to set profiles:

Go to system administratorà Profiles—system

Check user (Required Level) check boxà enter user name “RAMA2”

And enter profile name: “MO: Operating Unit” and click on find button

Go to user level and enter “Vision Germany”

Save

SITE: MO: Operating Unit: US

Application:

Responsibility: PO US RESP: MO: Operating Unit: US

PO IN RESP: MO: Operating Unit: IN

PO DE RESP: MO: Operating Unit: DE

USER: PADMAJA: MO: Operating Unit: US

JHANSI: MO: Operating Unit: IN

KHAJA: MO: Operating Unit: DE

In real time the profiles will set at “RESPONSIBILITY” Level

User level is the max precedence.

	User profile name (Functional consultant)	NAME (Technical Consultant)
1	MO: Operating Unit	ORG_ID
2	GL Ledger Id	GL_SET_OF_BKS_ID
3	GL Ledger Name	GL_SET_OF_BKS_NAME
4	HR:Business Group	PER_BUSINESS_GROUP_ID
5	ICX: Date format mask	ICX_DATE_FORMAT_MASK
6	ICX: Numeric characters	ICX_NUMERIC_CHARACTERS
7		USERNAME
8		USER_ID
9		RESP_ID
10		RESP_APPL_ID

How to read or get profiles in back end

FND_PROFILE.VALUE(‘PROFILE NAME’)

SELECT FND_PROFILE.VALUE(‘ORG_ID’) FROM DUAL;

SELECT FND_PROFILE.VALUE(‘GL_SET_OF_BKS_NAME’) FROM DUAL;

PADMAJA

OPERATIONS

1) PROC—DEVELOP

2) PROC—COMPILE

3) PADMAJA – EXE

4) PADMAJA – CP

5) PADMAJA – RG

6) PADMAHA –SWITCH RESP

7) PADMAJA—RUN CP ---MESSAGE O/P FILE

8) LOGIN WITH OPERATIONS USER

9) ADD RESPONSIBILITY “PO US RESP PADDU”

10) SWITCH RESP -- PO US RESP PADDU

11) RUN CP – MESSAGES LOG FILE

CREATE OR REPLACE PROCEDURE XXIM_GET_USERS(ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER) IS

BEGIN

IF FND_PROFILE.VALUE('USERNAME') = ‘PADMAJA’ TEHN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’…………’);

ELSE

FND_FILE.PUT_LINE(FND_FILE.LOG,’…………’);

END IF;

END;

Profiles tables:

SELECT * FROM Fnd_profile_options

SELECT * FROM Fnd_profile_option_values

How to submit a concurrent program from back end:

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

setserveroutput on;

declare

l_request_id number;

BEGIN

fnd_global.apps_initialize(1013700, --USER ID

66020, --RESP ID

201); --resp application id

--APPS INITIALIZATION --

l_request_id := FND_REQUEST.SUBMIT_REQUEST('PO', --APPLICATION SHORT NAME

'XXIM_DEPT_ENAME_RAMA', --CP SHORT NAME

'Emplyee report',--DESCRIPTION

sysdate,--start time

false, --subrequest

20,

'SCOTT');

commit;

dbms_output.put_line('l_request_id:-'||l_request_id);

end;

--SELECT * FROM FND_USER WHERE USER_NAME = 'RAMA2';

--SELECT * FROM FND_RESPONSIBILITY_VL WHERE responsibility_name = 'PO US RESP RAMA';

INTERFAE

====

FTP

SQL LOADER

PLSQL PACKAGE

SEEDED CP

FNDLOAD

======

DEV --

TEST -- DBA

PROD – DBA

It is an oracle utility to migrate AOL object(FND OBJECTS) from one instance (DEV) to another instance (TEST, UAT, and PROD).

FND Objects (AOL Objects)

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

Concurrent programs

Value sets

Users

Resp

Request group

Flex fields

Lookups

Form, Function, Menu

XML Publisher

Forms personalization

Request sets

Using FNDLOAD utility we are using DOWNLOAD KEYWORD to down load .ldt --Loader data file

DBA will use UPLOAD keyword to upload (Migrate) to required Instance

.ldt -- Loader data file

.lct -- Loader configuration files

cd /oraAS/oracle/VIS/apps/apps_st/appl

ls *.env

. APPSVIS_glo.env

cd $FND_TOP/patch/115/import

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct

XXIM_DEPT_ENAME_RAMA_CP.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="XXIM_DEPT_ENAME_RAMA"

Forms Personalization:

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

Customize or personalize forms using application (Oracle apps).

Navigation: Open required formà Help—diagnostic—Custom code—Personalize

Level of forms personalization:

Form

Function (Default)

POXPOEPO.fmb

POXPOEPO

POXPOEPO_IUD

POXPOEPO_READONLY

Triggers or events will support by forms personalization:

1) WHEN-NEW-FORM-INSTANCE

2) WHEN-NEW-BLOCK-INSTANCE

3) WHEN-NEW-RECORD-INSTANCE

4) WHEN-NEW-ITEM-INSTANCE

5) WHEN-VALIDATE-RECORD

6) MENU

7) SPECIAL

How to stop forms personalization code changes.

Help—diagnostic—Custom code—off

Action types:

1) Property (Make field mandatory, Hide, LOV, Font color, back ground color……)

2) Messages (Show (Popup), Hint (Status bar), Error, Warn, Debug)

3) Built-in (To call one form to another form, SRS window, Call procedure)

4) Menu (To add new menus)

Forms personalization table:

SELECT * FROM FND_FORM_CUSTOM_RULES

SELECT * FROM FND_FORM_CUSTOM_ACTIONS

='Hello user '||FND_PROFILE.VALUE('USERNAME')

Report Customization:

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

Suppliers Reports

Data Migration:

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

Legacy Application Third party Applications

Oracle Apps

Inbound Interface

Outbound Interface

DATA MIGRATION

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

Inbound Interface Outbound Interface

Interface Conversion

| |

Interface Table API Interface table API

Conversion:

=========

TUI:

2000 – TALLY

2022 – APPS R12

TUI

2000 – 11i

2022 – R12

Interface:

=======

IRCTCà Oracle apps

Real Integration

Scheduling

On demand

Conversion	Interface
To migrate Old data from legacy system to oracle apps	To migrate current transactions (Data) from legacy system to oracle apps
It is a onetime process	It’s a scheduling or on demand process or Real integration
We know data volume	We don’t know data volume
Need to prepare CV040 and CV060 documents	Need to prepare MD050 and MD070 documents

CV—Conversion (CV040--Mapping document)

ITEM CODE – ITEM

WARE HOUSE – INVENTOY ORGANIZATION

Base Table

Interface table approach:

Error table

Legacy system

Items.csv

Develop plsql package

Oracle apps Middle Tier

/inbound/items/in

Create stage table and load data into stage table

Shell script (ftp) Sql*Loader and write validations

Insert into Interface table

And insert into interface table Submit seeded

Program

Base Table

API approach:

Legacy system

Items.csv

Develop plsql package

Oracle apps middle tier

/inbound/items/in

Create stage table and load data into stage table

Shell script (ftp) Sql*Loader and write validations

Call API

And call API

Will get error messages using out parameters

Conversion: 3 million data

Interface Table: We can use bulk collect, Bulk insert/Update/Delete

Using correction forms we will correct data and reprocess data

The error messages will store in error table

API: Give status for each record

Limit: 10,000

20,000

100000

10 times

Sqlengine --plsql engine

Out parameters in API:

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

X_RETURN_STATUS:

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

S – Success

E – Error

U – Unexpected error

X_MSG_COUNT:

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

It will give error count for each record

X_MSG_DATA

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

It will return error message

ITEM Interface: org code, template name (MIX, Purchased itemsxx)

Interface table approach:

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

1) Get data file

2) Transfer data file from local machine to server

3) Create stage table in custom schema

4) Create synonym in APPS schema

5) Develop .ctl file

6) Transfer .ctl file from local machine to server

7) Define executable, CP, attach cp to RG

8) Change resp

9) Submit program

10) Develop pl/sql package and write validations and insert into interface table

11) Compile package

12) Define executable, CP, assign CP to RG

13) Change resp

14) Run program through SRS window

15) Data will load into interface table

16) Run seeded concurrent program

17) The data will go to either base table or error table

API approach:

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

1) Get data file

2) Transfer data file from local machine to server

3) Create stage table in custom schema

4) Create synonym in APPS schema

5) Develop .ctl file

6) Transfer .ctl file from local machine to server

7) Define executable, CP, attach cp to RG

8) Change resp

9) Submit program

10) Develop pl/sql package and write validations and call API

11) Compile package

12) Define executable, CP, assign CP to RG

13) Change resp

14) Run program through SRS window

15) The data will go to either base table if record is correct else will get messages using out parameters

ITEMS Interface:

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

Interface table:

MTL_SYSTEM_ITEMS_INTERFACE

Base Tables:

MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_TL

Error Table:

MTL_INTERFACE_ERRORS

Seeded Program Name:

Import Items

PO INTREA

115mfgapi.pdf

Items Interface:

Create table INV.XXIM_ITEMS_STG(

ORGANIZATION_CODE VARCHAR2(3),

ITEM VARCHAR2(40),

DESCRIPTION VARCHAR2(240),

TEMPLATE_NAME VARCHAR2(30),

TRANSACTION_TYPE VARCHAR2(10),

CREATED_BY NUMBER,

CREATION_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

REQUEST_ID NUMBER,

RECORD_STATUS VARCHAR2(10),

ERROR_MESSAGE VARCHAR2(4000) )

CREATE OR REPLACE SYNONYM XXIM_ITEMS_STG FOR INV.XXIM_ITEMS_STG

load data

infile "/oraAS/oracle/VIS/apps/apps_st/appl/inv/12.0.0/in/ITEMS_DATA.txt"

append into table XXIM_ITEMS_STG_RAMU

fields terminated by ","

optionally enclosed by '"'

(ORGANIZATION_CODE ,

ITEM ,

DESCRIPTION,

TEMPLATE_NAME ,

TRANSACTION_TYPE)

CREATE OR REPLACE PACKAGE XXIM_ITEMS_CREATE_STG IS

PROCEDURE CREATE_ITEMS(ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY XXIM_ITEMS_CREATE_STG IS

procedurecreate_items(errbuf out varchar2,

RETCODE OUT NUMBER) IS

l_organization_id NUMBER;

l_error NUMBER;

l_error_messageVARCHAR2(4000);

l_template_nameVARCHAR2(30);

BEGIN

for rec in (SELECT stg.*,

rowidrow_id

FROM XXIM_ITEMS_STG_RAMU stg

WHERE record_status IS NULL)

LOOP

l_error := 0;

l_error_message := NULL;

l_template_name := NULL;

BEGIN

SELECT organization_id

INTO l_organization_id

FROM MTL_PARAMETERS

WHERE organization_code = rec.organization_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error := 1;

l_error_message := 'organization code not exists in oracle, ';

WHEN OTHERS THEN

l_error := 1;

l_error_message := 'error while validating organization code:- '||SQLERRM;

END;

BEGIN

SELECT template_name

INTO l_template_name

FROM MTL_ITEM_TEMPLATES

WHERE template_name = rec.template_name;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error := 1;

l_error_message := l_error_message||'template_name not exists in oracle, ';

WHEN OTHERS THEN

l_error := 1;

l_error_message := l_error_message||'error while validating template_name:- '||SQLERRM;

END;

IF l_error = 0 THEN

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE

(organization_code,

segment1,

description,

template_name,

transaction_type,

process_flag,

created_by,

creation_date,

last_updated_by,

last_update_date,

request_id)

VALUES(rec.organization_code,

rec.item,

rec.description,

rec.template_name,

rec.transaction_type,

FND_GLOBAL.USER_ID,

SYSDATE,

FND_GLOBAL.USER_ID,

SYSDATE,

FND_GLOBAL.CONC_REQUEST_ID);

UPDATE XXIM_ITEMS_STG_RAMU

SET record_status = 'S'

WHERE rowid = rec.row_id;

ELSE

UPDATE XXIM_ITEMS_STG_RAMU

SET record_status = 'E',

error_message = l_error_message

WHERE rowid = rec.row_id;

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'error in the procedure create_items:-'||sqlerrm);

retcode := 2;

ROLLBACK;

END;

SELECT * FROM XXIM_ITEMS_STG_RAMU;

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

SELECT * FROM MTL_INTERFACE_ERRORS WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

CREATE TABLE INV.XXIM_INV_STOCK_LOCATOR_STG

(ORGANIZATION_CODE VARCHAR2(3),

SUBINVENTORY_CODE VARCHAR2(10),

CONCATENATED_SEGMENTS VARCHAR2(800),

DESCRIPTION VARCHAR2(50),

CREATED_BY NUMBER,

CREATION_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

REQUEST_ID NUMBER,

RECORD_STATUS VARCHAR2(1),

ERROR_MESSAGE VARCHAR2(4000) );

CREATE OR REPLACE SYNONYM XXIM_INV_STOCK_LOCATOR_STG FOR INV.XXIM_INV_STOCK_LOCATOR_STG;

M1|FGI|1.5.BOLTS|BOLTS LOCATOR|

M1C|FGICC|1.5.BOLTS|BOLTS LOCATOR|

M1|FGI|1.6.NUTS|BOLTS LOCATOR|

LOAD DATA

INFILE "/oraAS/oracle/VIS/apps/apps_st/appl/inv/12.0.0/data/STOCK_LOCATOR.txt"

APPEND INTO TABLE XXIM_INV_STOCK_LOCATOR_STG

FIELDS TERMINATED BY "|"

(

ORGANIZATION_CODE ,

SUBINVENTORY_CODE ,

CONCATENATED_SEGMENTS ,

DESCRIPTION)

CREATE OR REPLACE PACKAGE XXIM_STOCK_LOC_PKG IS

PROCEDURE CREATE_LOCATOR(ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY APPS.XXIM_STOCK_LOC_PKG IS

PROCEDURE CREATE_LOCATOR(ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER) IS

l_error NUMBER;

l_error_messageVARCHAR2(4000);

l_organization_id NUMBER;

l_secondary_inventory_name VARCHAR2(10);

L_RETURN_STATUS VARCHAR2(1);

L_MSG_COUNT NUMBER;

L_MSG_DATA VARCHAR2(4000);

L_INVENTORY_LOCATION_ID NUMBER;

l_locator_existsVARCHAR2(1);

BEGIN

FOR REC IN (SELECT STG.*,

ROWID ROW_ID

FROM XXIM_INV_STOCK_LOCATOR_STG stg

WHERE record_status IS NULL)

LOOP

l_error := 0;

l_error_message := NULL;

l_organization_id := NULL;

l_secondary_inventory_name := NULL;

BEGIN

SELECT organization_id

INTO l_organization_id

FROM MTL_PARAMETERS

WHERE organization_code = rec.organization_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error := 1;

l_error_message := 'organization code not exists in oracle, ';

WHEN OTHERS THEN

l_error := 1;

l_error_message := 'error while validating organization code, '||SQLERRM||', ';

END;

IF l_organization_id IS NOT NULL THEN

BEGIN

SELECT secondary_inventory_name

INTO l_secondary_inventory_name

FROM MTL_SECONDARY_INVENTORIES

WHERE organization_id= l_organization_id

AND secondary_inventory_name = rec.subinventory_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error := 1;

l_error_message := l_error_message||'subinventory_code not exists in oracle, ';

WHEN OTHERS THEN

l_error := 1;

l_error_message := l_error_message||'error while validating subinventory_code, '||SQLERRM;

END;

END IF;

IF l_error = 0 THEN

INV_LOC_WMS_PUB.CREATE_LOCATOR (L_RETURN_STATUS,

L_MSG_COUNT,

L_MSG_DATA,

L_INVENTORY_LOCATION_ID,

L_LOCATOR_EXISTS,

L_ORGANIZATION_ID,

rec.organization_code,

rec.concatenated_segments,

REC.DESCRIPTION,

NULL,

rec.subinventory_code,

NULL,

NULL) ;

fnd_file.put_line(fnd_file.LOG,'L_RETURN_STATUS:-'||L_RETURN_STATUS);

fnd_file.put_line(fnd_file.LOG,'L_MSG_COUNT:-'||L_MSG_COUNT);

fnd_file.put_line(fnd_file.LOG,'L_MSG_DATA:-'||L_MSG_DATA);

fnd_file.put_line(fnd_file.LOG,'L_INVENTORY_LOCATION_ID:-'||L_INVENTORY_LOCATION_ID);

fnd_file.put_line(fnd_file.LOG,' ');

UPDATE XXIM_INV_STOCK_LOCATOR_STG

SET record_status = 'S'

WHERE rowid = rec.row_id;

ELSE

UPDATE XXIM_INV_STOCK_LOCATOR_STG

SET record_status = 'E',

error_message = l_error_message

WHERE rowid = rec.row_id;

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'error in the procedure CREATE_LOCATOR:-'||SQLERRM);

ROLLBACK;

RETCODE := 2;

END;

SELECT * FROM XXIM_INV_STOCK_LOCATOR_STG;

SELECT * FROM MTL_ITEM_LOCATIONS WHERE TRUNC(SYSDATE) = TRUNC(CREATION_DATE);

CREATE OR REPLACE PROCEDURE XXGET_SAL_ENAME( IN NUMBER, X_SAL OUT NUMBER, OUT VARCHAR2) IS

BEGIN

SELECT ENAME,SAL

INTO X_ENAME,X_SAL

FROM EMP WHERE EMPNO = P_EMPNO;

END;

setserveroutput on;

DECLARE

l_sal number;

l_ename varchar2(10);

begin

XXGET_SAL_ENAME(X_SAL =>l_sal, P_EMPNO => 7900, X_ENAME =>l_ename);

dbms_output.put_line(l_sal||' '||l_ename);

end;

Forms development:

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

1) Setup forms path

A) Download TEMPLATE.fmb and APPSTAND.fmb from au/12.0.0/forms/US and transfer to local machine (forms path)

B) Download .pll and .plx from au/12.0.0/resource and transfer to local machine (forms path)

AU – Application Utilities

2) Create table in custom schema

3) Create synonym in apps schema

4) Develop new form using TEMPLATE.fmb

5) Transfer new form (.fmb) from local machine to server (Custom top/forms/US)

6) Compile forms using putty tool and generate .fmx (command – frmcmp_batch)

7) Register form

8) Register function

9) Create a new menu and attach function to menu

10) Attach menu to resp

11) Change resp

12) Test (Open the form and insert new records, save)

13) Check data in table

5000

CHARGE A/C

LIABILITY

30 DAYS

5000 – DR CHARGE A/C

5000 – CR Liability A/C

5000 – CR Payment clearance

5000 – DR Liability A/C

1 DR = 1 CR

5000 DR = 5000 CR

XML Publisher reports/BI Publisher reports

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

Advantages of XML publisher report

1) Layout design is simple

2) The user can change o/p format at run time (.pdf, excel, hmtl, .rtf)

3) We can create multiple templates and assign to program

4) XML bursting (To send email, printer, fax, ftp)

Ways to develop XML Publisher reports

1) .rdf

2) Data template

3) Pl/sql stored procedure

XML publisher report using data template

1) Develop data template (.xml)

2) Create concurrent program (EXE NAME: XDODTEXE, op format àXML)

3) Add responsibility “ XML Publisher Administrator”

4) Create data definition and upload data template (.xml) using the responsibility ”XML Publisher Administrator”

CP SHORT NAME = CODE IN UPPER CASE (Data definition code)

5) Attach cp to rg

6) Run cp through SRS WINDOW

7) Click on view output button and download .xml file to local machine

8) Open MS word documents (We should install BI Publisher desktop) and upload .xml file and develop template and save as .rtf (Rich text format)

9) Register template and upload template (.rtf) using ”XML Publisher Administrator”

10) Change resp

11) Run cp through SRS window

Important XML tags:

1) Data template name

2) Properties

3) Parameters

4) dataQuery (sqlStatement)

5) dataStructure (group)

6) triggers (Before report and after report)

<?xml version = '1.0' encoding = 'UTF-8'?>

</properties>

</parameters>

<![CDATA[SELECT INVOICE_NUM,

INVOICE_ID,

INVOICE_AMOUNT

FROM AP_INVOICES_ALL]]>

</sqlStatement>

</dataQuery>

</group>

</dataStructure>

</dataTemplate>

NALAM Oracle Apps Technical

Monday, 4 March 2024

Oracle Apps Technical knowledge

No comments:

Post a Comment