GL INTERFACES

                      GL INTERFACES


                    Difference Between Interface and API

An API (Application Programming Interface) is inbuilt program through which data’s can be transferred to Oracle base tables directly without writing the program for  validating or inserting in Interface tables.


But through User Interface, we have to write codes for validation and insertion of data’s In Interface tables and then in Oracle base tables


Oracle defines an API as "A set of public programmatic interface that consist of a language and message format to communicate with an operating system or other programmatic environment, such as databases, Web servers, JVMs,  and so forth. These messages typically call functions and methods available for application development."I guess that there is no such thing as an interface to Oracle=2 there are only "open" interfaces.


These are a group of published and supported inbound and outbound Interface objects (talk about them more in a second) that Oracle has developed for your use=2 these objects may be, a table or group of tables, a concurrent program that references a PL*SQL or Pro*C/C++ package o library.


Oracle's policy over the years is that if you post data directly to an application table, then you are invalidating your support agreement, and if you are bored enough, you can see the clause in the agreement sure enough.


But these published interfaces are quite often not enough to d what you need to do. Take the case of Payables suppliersfor example. That has been a pain for everyone Right from the beginning.


So in answer to your question. An open interface is a group of objects that Oracle supports and licenses with the software that allow inbound and outbound data transactions in Oracle' Approved format. You put data into the tables and oracle import it or you run a concurrent request and oracle outputs data into that table. Batch processing. You need to put your data into the right pre-validated format before inserting into the interface table or the data will be rejected.


An API is programmatic hooks or coding blocks that you can "call that allow you to perform some function or other to achieve par of your goal. For example, Oracle Projects there is PA_INTERFACE_UTILS_PUB.CREATE_PROJECT API that takes a list o input variables that you assign to the called API (these can b in a table or passed directly) for example;
l_project_in.created_from_project_id := '1001'; -- Project id from template
l_project_in.project_name :='C, AMG TEST PROJECT';
l_project_in.pm_project_reference := 'C, AMG TEST PROJECT';
and if the package passes validation on all these data points your project will be inserted as a result.

So finally, you can insert data into an open interface table then use a concurrent program that CALLS an API to process that information.

In the case of real-time processing or OLTP, once you save record in PeopleSoft HR, you want it to appear in Oracle H immediately, you would write PeopleSoft code that copies those data items to memory, submit the list of required variable (PeopleSoft data)to the Oracle API as inputs. The API o completion posts your employee into theOracle database=2 (ideally you would provide a function then back to PeopleSoft t confirm that oracle received the transaction correctly and i wasn't rejected by the API). All this was achieved without the use of an open "interface".

The nail in t he coffin however and where you may be confused, i that many people think that any exchange of data from one table to another is an "interface". A "link".


And I guess that it i in a way.
Just remember that An interface is the pool,
An open interface table is the lanes and
An API is the swimmer.

The term Open Interfaces actually refers to table driven interfaces, where you put data in
a table that sits between your external application and the Oracle module you are interfacing with.
The term API refers to stored procedure driven interfaces, where you call a stored procedure
to perform an action within an Oracle Module, and the data from your external application is
passed through the stored procedure’s parameters. Historically, there were only table driven
interfaces and they were called Open Interfaces. Later, stored procedures were added and were called APIs.

GL Interface
Interface tables:
GL_INTERFACE

Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

Staging Table:
Create table XX_GL_RY_STG
(status varchar2(50),
set_of_books_id number(15),
User_JE_Source_name varchar2(25),
user_je_category_name varchar2(25),
currency_code varchar2(15),
actual_flag char(1),
ACCOUNTING_DATE date,
DATE_CREATED date,
CREATED_BY number(15),
entered_dr number,
entered_cr number,
accounted_dr number,
accounted_cr number,
segment1 varchar2(25),
segment2 varchar2(25),
segment3 varchar2(25),
segment4 varchar2(25),
segment5 varchar2(25)
);
insert into XX_GL_RY_STG values(
'NEW',1,'Manual' ,'Adjustment','USD','A','20-MAR-2009' ,'20-MAR-2009', 2000,2000,2000,2000,
'01','000','9950','2080','000','0')
Package:
CREATE OR REPLACE PACKAGE XX_GL_INT_RY_PKG
IS
PROCEDURE xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2);
END;
CREATE OR REPLACE Package body XX_GL_INT_RY_PKG
is
Procedure xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2)
is
cursor GL_CUR
IS
SELECT Status,set_of_books_id,User_JE_Source_name,
user_je_category_name,currency_code,actual_flag,
ACCOUNTING_DATE,DATE_CREATED,CREATED_BY,entered_dr,
entered_cr,accounted_dr,accounted_cr,
segment1, segment2, segment3, segment4, segment5
FROM XX_GL_RY_STG;
lv_status varchar2(50);
lv_sob_id Number(15);
lv_user_je_source_name varchar2(25);
lv_user_je_category_name varchar2(25);
lv_cur_code varchar2(15);
lv_actual_flag varchar2(1);
lv_err_flag varchar2(2);
lv_flag varchar2(2);
BEGIN
FOR rec in GL_CUR
LOOP
lv_flag := 'A';
lv_err_flag := 'A';
BEGIN
SELECT distinct Status into lv_status from XX_GL_RY_STG Where status = 'NEW';
EXCEPTION
When no_data_found Then
lv_status := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The status is not correct so change the status');
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_status );
END;
BEGIN
SELECT set_of_books_id into lv_sob_id from gl_sets_of_books
where set_of_books_id=rec.set_of_books_id;
Exception
When no_data_found Then
lv_sob_id:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOB is not correct change SOB ID');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_sob_id );
BEGIN
SELECT user_je_source_name into lv_user_je_source_name FROM GL_JE_SOURCES
WHERE user_je_source_name=rec.user_je_source_name;
EXCEPTION
WHEN no_data_found THEN
lv_user_je_source_name := NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOURCE NAME is not correct change It');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_source_name );
BEGIN
SELECT user_je_category_name INTO lv_user_je_category_name FROM GL_JE_CATEGORIES
where user_je_category_name=rec.user_je_category_name;
EXCEPTION
When no_data_found Then
lv_user_je_category_name:=NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Category name is not correct Change it');
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_category_name );
END;
BEGIN
SELECT currency_code into lv_cur_code from FND_CURRENCIES
where currency_code=rec.currency_code;
Exception
When no_data_found Then
lv_cur_code:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency code is not correct ');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_cur_code);
BEGIN
SELECT ACTUAL_FLAG into lv_actual_flag from XX_GL_RY_STG
where actual_flag in ('A','B','E');
Exception
When no_data_found then
lv_actual_flag := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Flag is not correct');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The dat inserting is... '|| lv_actual_flag);
IF lv_flag = 'A' THEN
INSERT into GL_INTERFACE (
STATUS, SET_OF_BOOKS_ID, USER_JE_SOURCE_NAME ,USER_JE_CATEGORY_NAME,
CURRENCY_CODE,ACTUAL_FLAG,
ACCOUNTING_DATE, DATE_CREATED,CREATED_BY, ENTERED_DR,ENTERED_CR,
ACCOUNTED_DR,ACCOUNTED_CR,segment1, segment2, segment3, segment4, segment5)
VALUES (
lv_Status, lv_sob_id, lv_User_JE_Source_name, lv_user_je_category_name,
lv_cur_code,lv_actual_flag,rec.ACCOUNTING_DATE, rec.DATE_CREATED,
1318,rec.entered_dr, rec.entered_cr, rec.accounted_dr,rec.accounted_cr,
rec.segment1, rec.segment2, rec.segment3, rec.segment4, rec.segment5);
END IF;
lv_flag :=null;
lv_err_flag:=null;
END LOOP;
COMMIT;
End;
END XX_GL_INT_RY_PKG;
/

Base tables for GL Daily Rates are—
Gl_DAILY_RATES

Interface table for GL Daily Rates are—
Gl_DAILY_RATES_INTERFACE

Moving the Data from Flat File to Base Table using SQL * LOADER:
Options (Skip =0)
Load data
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table GL_daily_rates_stg
fields terminated by ','
optionally enclosed by '"'
Trailing nullcols
(From_currency ,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate, Mode_flag)

Moving the data from Staging tables to Base Tables using
Standard Interface Programs:
Create a Staging table based on the requirement
CREATE TABLE XXGL_DRATES_STG (
FROM_CURRENCY VARCHAR2(15),
TO_CURRENCY VARCHAR2(15),
FROM_CONVERSION_DATE DATE,
TO_CONVERSION_DATE DATE,
USER_CONVERSION_TYPE VARCHAR2(30),
CONVERSION_RATE NUMBER,
MODE_FLAG CHAR(1));

Inserting Data into Staging Table:
Insert into XXGL_DRATES_STG Values (
'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

Create a Package with validations to move the data into Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number,errbuff out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number, errbuff out varchar2)
Is
Cursor cur_drates is
Select FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE , TO_CONVERSION_DATE ,
USER_CONVERSION_TYPE, CONVERSION_RATE , MODE_FLAG FROM XXGL_DRATES_STG;
LV_FROM_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LV_ERR_FLAG VARCHAR2(1):= 'A';
BEGIN
FOR i IN CUR_DRATES
LOOP
BEGIN
Select CURRENCY_CODE into LV_FROM_CURRENCY FROM
FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_FROM_CURRENCY );
BEGIN
Select CURRENCY_CODE into LV_TO_CURRENCY
FROM FND_CURRENCIES where ENABLED_FLAG='Y'
AND CURRENCY_CODE=i.To_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_TO_CURRENCY );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
IF LV_ERR_FLAG='A' THEN
INSERT INTO GL_DAILY_RATES_INTERFACE (
FROM_CURRENCY, TO_CURRENCY,
FROM_CONVERSION_DATE, TO_CONVERSION_DATE,
USER_CONVERSION_TYPE, CONVERSION_RATE,
MODE_FLAG)
VALUES (
LV_FROM_CURRENCY,LV_TO_CURRENCY,
I.FROM_CONVERSION_DATE, I.TO_CONVERSION_DATE
, LV_USER_CONVERSION_TYPE, I.CONVERSION_RATE
, I.MODE_FLAG);
END IF;
END LOOP;
COMMIT;
END;
END XXGL_DRATES_PKG;

Create an Executable – XXGL_DRATES_PKG_EXEC
Execution File
Create a Concurrent program – XXGL_DRATES_PKG_EXEC IFace Conc prg
Add the Conc program to the Request group
In custom module, Run the Conc Program thro’ SRS Window.
In GL MODULE Run the Standard Concurrent Program –
Program - Daily Rates Import and Calculation

Complete GL Interface

Complete GL Interface



Complete GL Interface
 Pre-Requisites:
    --------------
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined


    Interface Tables:
    ----------------
      GL_INTERFACE

    Base Tables:
    -----------
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES


 Standard Program:
 Go to General Ledger Vision Operations(U.S.A)
   Run =>Import
 Here Give the Source name and Save.
While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there...........


   Validation Columns:
   ------------------
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id


    Source = 'NEW'
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.


Control file for GL_Interface:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(STATUS,
 SET_OF_BOOKS_ID,
 ACCOUNTING_DATE,
 CURRENCY_CODE,
 DATE_CREATED,
 CREATED_BY,
 ACTUAL_FLAG,
 USER_JE_CATEGORY_NAME,
 USER_JE_SOURCE_NAME,
 SEGMENT1,
 SEGMENT2,
 SEGMENT3,
 SEGMENT4,
 SEGMENT5,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 GROUP_ID)

BEGINDATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl'    log='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log'

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;

procedure main(errbuf out varchar2,
               retcode out varchar2
               )
 is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin

   dis_log('before entering the loop');
 

 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:='S';
                v_tot_err_msg:=null;
                v_gl_int:=null;
              --currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid Currency Code =>'||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at Currency Code =>'||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;    
             
             
                --user_je_source_name validation
             
                begin
             
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid Sourec Name =>'||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at Sourec Name =>'||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;    
             
                --category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid category_name =>'||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at category_name =>'||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;  
               
                end;
             
                 --user id validation
               
                begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid user id =>'||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at user id =>'||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 
                end;
             
                 -- set of books id validation
               
                begin
               
                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid set of books id =>'||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception atset of books id =>'||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;
           
                         v_gl_int.status                    :=i.status;
                        -- v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        -- v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         --v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         --v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        --v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                       
             
               
                 dis_log('before inserting the loop');
               
                   if v_process_flag = 'S' then    
             
                    insert into gl_interface values v_gl_int;
               
                   end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
               
                 dis_log('after inserting the loop');    
 end loop;
 exception
 when others then
 dis_log('exception occured at main loop');
 end main;
 end xx_gl_int_pkg;

Purchase Order - Module

Purchase Order Application: Group of Forms, Reports and Programs

Requisition
Request for Quotation (RFQ)
Quotation
Purchase Order
Receipts
AP Interface
INV Interface
Purchase
Documents

Before going to start PO application we have to define following things
Items Creation
Suppliers creation
Buyer creation

1) Item Creation
a. Open the user from in System Administrator Responsibility attach the responsibility called Inventory, Vision Operations(USA)
b. Open the Items from - Items – Master Item – enter, system will show the organization list, select the organization vision operations, enter the item name and description.
c. Go to Inventory tab check the check box called Inventory Item.
d. Go to purchasing tab check the check box called purchased and save the transactions.
e. We can assign the item to the multiple organization by selecting tool menu – Organization assignment option check the check box called assigned.

Select * from ORG_ORGANIZATION_ DEFINETIONS
- Organizatio_ID is primary key column

Select * from MTL_SYSTEM_ITEM_S
Where sgment1=’Keyboad’
- Inventory_item_ID -
- Organizatin_ID - Composite Primary Key

Select MSI.SEGMENT ITEM
OOD.ORGANIZATION_NAME
From MTL_SYSTEM_ITEM_B MSI
ORG_ORGANIZATION_DEFINETIONS OOD
Where MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
And MSI.SEGMENT1 = ‘KEYBOARD’

2) Supplier Creation
a. Select the responsibility called Purchasing, Vision Operations(USA)
b. Open the suppliers form - Supply Base – Suppliers press enter
c. Enter the suppliers name and save the transaction and we will get supplier number select status button enter site address select contact tab – enter contact details and save

Select * from PO_VENDORS where SEGMENT1=’5080’
- VENDOR_ID – Primary Key
Select * from PO_VENDOR_SITESM_ALL where VENDOR_ID=’1010’
- VENDOR_SITE_ID – Primary Key
Select * from PO_VENDOR_CONTACTS
where VENDOR_SITE_ID= ‘2215’
- VENDOR_CONTACT_ID – Primary Key
CLASS - 13

3) Buyer Creation:
a. Attach the Responsibility called HRMS Management; Open the Employee form (N) HRMS Manager – People – Enter and Maintain.
b. Select new button enter employee name and Date of Birth, select action as creation employment chose the option called buyer and save the transaction.

Select * from PER_ALL_PEOPLE_F
Where EMPLOYEE_NUMBER = ‘893’

PERSON_ID is Primary Key column

c. Go to system administrator create user name attach employee name in the filed called Person and save the transaction.
Select FU. USER_NAME,
PPF.FULL_NAME
From FND_USER FU,
PER_ALL_PEOPLE_F PPF
Where FU.USER_NAME = ‘20USER’
And FU.EMPLOYEE_ID = PPF.PERSON_ID


d. Copy the employee name go to purchasing application open the Buyers form attach to the Buyers list - Setup – Personnel – Buyers and enter.

Select * from PO_AGENTS

This contains only Buyer list and primary key is AGEN_ID

PO PROCESS

1) Requisition: It is one of the purchasing document will be crated by employers when ever they required goods or services or training, we will find 2 types of requisitions.
a. Internal Requisition: This will be created, when we receiving the materials from other organizations(Branches)
b. Purchase Requisition: This will be created, when we are receiving the materials from outside means suppliers.
c. We will enter the requisition information at 3 levels i.e. Headers, Lines, Distributions.
d. One header will be there at least one line multiple line we create for every line at least one distribution or multiple distributions will be there.
e. Open the requisitions form – Requisitions – Requisitions and enter.
f. Select the requisition type at header level enter item details at line level (item Name, quantity, price and need by date) select bistributions buttion enter distribution details like quantity and account details and save the transactions.
g. Select approve button press OK system will send the document for approval.
h. Go to requisition number select find button, it will show requisition approved status.

Select * from PO_REQUISITON_HEADERS_ALL
WHERE SEGMENT1 = ‘1656’
Primary key - REQUISITION_HEAD_ID

Select * from PO_REQUISTION_LINES_ALL
WHERE REQUISITION_HEADER_ID = ‘11458’
Primary Key - REQUISITION_LINE_ID

Select * from PO_REG_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID= 9922
Primary Key - DISTRIBUTION_ID

i. Cancel the Requisition: Open the requisition summery form enter requisition number select find button go to tools menu select option called ‘Control’ and chose the option as cancel requisition.

Select AUTHORIZATION_STATUS from PO_REQUISITION_HEADER_ALL

j. We can find out requisition history by using tools menu option called view action history.
Select * from PO-ACTION_HISTORY
Where OBJECT_ID = REQUISITION_HEADER_ID

Select PAH.ACTION_CODE
PDF.FULL_NAME
From PO_ACTION_HISTORY PAH
PER_ALL_PEOPLE_F PPF
Where OBJECT_ID = 11459
And PAH.EMPLOYEE_ID = PPF.PERSON_ID

k. Enter manual requisition Number: Setup – Organization – Purchasing Options and enter - go to numbering tab select requisition number entry as manual type.
CLASS - 14

2) RFQ – REQUEST FOR QUOTATION: It is one of the purchasing document after requisition is approved. We will go for creation of RFQ we have 3 types of RFQs.
a. Bid : If company is going to purchase large number of items which are expensive. We will create bid RFQ where we will specify Headers, lines and shipments, where we are not specify any price breaks in Bid RFQ.
b. Catalog : If company is purchasing materials regularly fixed quantity location and date, then we will select regularly we can include price breaks at different levels.
c. Standard : This will be created for items we need only once or not regularly, we can include price breaks at different quality levels.

3) QUOTATIONS : After creation of RFQ document we will send this document to the different suppliers who are going to supply the materials, next we will receive quotations from the suppliers either by email or fax or by phone,
a. Again quotation are 3 types i.e. Bid, Catalog and Standard
b. What ever the quotation we are received from the suppliers we will enter those quotation details in the system for future purpose.
c. If we are sending bid RFQ to the suppliers we will receive the bid quotation for catalog RFQ we will receive catalog quotation for standard RFQ we will receive standard quotation.

d. RFQ – Terms and conditions.
i. Payment Terms – At the time of creating RFQ document we will specifying the payment terms like due date, interest rates and so on.
ii. Fright Terms: It is nothing by at transportation charges whether supplier will bear it or buyer.
iii. FOB(Foot on Board) : It is responsibility of material damage or material missing during the transportation.
iv. Carrier : We will give the transportation company name so that supplier will supply the materials by this transportation company.
e. Navigation – Open the RFQ form – RFQs and Quotation – RFQs and enter - select RFQ type from header level enter ship to location, Bill to location details – select line level enter item details select price breaks button enter price break details select terms button enter terms and conditions details, select suppliers button and enter supplier name who are going to receive RFQ document

Select * from PO_HEADERS_ALL Where segment1=’306’
Primary Key- PO_HEADER_ID

Select * from PO_LINES_ALL Where PO_HEADERS_ID = ‘11845’
Primary Key – PO_LINE_ID

Select * from PO_LINE_LOCATIONS_ALL where PO_LINE_ID 12233
Primary Key –LINE_LOCATION_ID

Select * from FND_CURRIENCIES
Primary Key –CURRENCY_CODE

Select * from AP_TERMS
Primary Key – TERM_ID

Select * from AP_TERMS_LINES

Select * from HR_LOCATIONS
Primary Key – LOCATION_ID

f. AUTO CRATE OPTION : This is one of the feature application to create RFQ or PO documents automatically based on the approved requisition document.
i. Create requisition and approve it
ii. Open the auto create form select clear button enter requisition number select find button which will display requisition then details
iii. Select the line by checking the checkbox select action as create, document types as RFQ
iv. Select automatic button click the create button whichwill create RFQ document will selected requisition lines and displays RFQ No.
g. QUOTATION : - RFQs and Quotation – Quotation and enter
i. We will receive the quotations from suppliers against the RFQ either by fax or email we will enter those quotation details manually in the system to make the quote analysis and future purpose.
ii. Quotation tables: One we create the quotation from front end data will be stored in the RFQs tables only by column type_lookup_code=’quotation’.

Select * from PO_HEADERS_ ALL
WHERE SEGMENT1 = ’500’
AND TYPE_LOOKUP_CODE =’QUOTATION’
CLASS - 15

4) Purchase Orders : It is one of the purchasing document at the time of purchasing from the supplier we will create this document by specifying terms and conditions and shipping details distribution details and so on. We have 4 types of Purchase Orders

a. Standard
b. Planned
c. Blanket
d. Contract

Purchase Order Types Summery

Standard Purchase Order

When we require the materials from suppliers we will cerate standard PO by specifying terms and conditions price, quantity and so on.

Select * from PO_HEADERS_ALL
where segment1=’3445’ and type_lookup_id = ‘STANDARD’

Select * from Po_lines_all where PO_Header_id = 11858

Select * from PO_Line_locations_all where Po_line_id=12216
Primary Key – LINE_LOCATION_ID

Select * from po_distributions_all where line_location_id
Primary Key – PO_DISTRIBUTION_ID

(N) – Open the purchase order form – Purchase Orders-Purchase Orders and enter select PO type and suppliers information enter the line level details like Items quantity price and so on.
Select shipments button enter shipping location details quantity promice date, need by date and so on select distribution button enter distribution details and save the transactions.
Select approve button system will submit the document for approvals.
Copy the PO number and go to purchase or summary form enter PO number select find button.
To cancel the purchase order go the tools menu – control option.
Tools menu copy document to create the same document.
Auto Create
By using Auto Create option we can create purchase orders automatically from approved requisition by selecting document type as Purchase order.

SELECT PHA.SEGMENT1 PONUM,
PHA.TYPE_LOOKUP_CODE POTYPE,
TRUNC(PHA.CREATION_DATE) CDATE,
PV.VENDOR_NAME SUPPLIER,
PVS.VENDOR_SITE_CODE SUPPLIERSITE,
(PVC.FIRST_NAME ','pvc.LAST_NAME) Contact,
HL1.LOCATION_CODE ShipTO,
HL2.LOCATION_CODE BillTo,
PHA.CURRENCY_CODE Currency,
PPF.FULL_NAME Buyer,
PHA.AUTHORIZATION_STATUS PoStatus,
SUM((PLA.QUANTITY*PLA.UNIT_PRICE)) LineLevelPrice,
PHA.COMMENTS PODesc
FROM PO_HEADERS_ALL PHA ,
PO_VENDORS PV ,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
HR_LOCATIONS HL1,
HR_LOCATIONS HL2,
PER_ALL_PEOPLE_F PPF,
PO_LINES_ALL PLA
WHERE PHA.SEGMENT1 = '3449'
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID
AND PHA.SHIP_TO_LOCATION_ID = HL1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = HL2.LOCATION_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
GROUP BY
PHA.SEGMENT1 ,
PHA.TYPE_LOOKUP_CODE ,
TRUNC(PHA.CREATION_DATE),
PV.VENDOR_NAME ,
PVS.VENDOR_SITE_CODE ,
(PVC.FIRST_NAME ','PVC.LAST_NAME) ,
HL1.LOCATION_CODE ,
HL2.LOCATION_CODE ,
PHA.CURRENCY_CODE ,
PPF.FULL_NAME ,
PHA.AUTHORIZATION_STATUS ,
PHA.COMMENTS



CLASS - 16

Blanket Purchase Order

When ever company would like to have the agreement with supplier that time first we will create Blanket Agreement, when ever we require materials we will go for releasing the purchase orders.
Open the purchase order form select Blanket Purchase agreement and enter the details and approve the purchase order.
Select releases form either blanket PO Number and Item quantity details select distributions button enter distribution details, select approve button for approvals.

Match Approval

2 way
PO Qty – 100


Invoice Qty 80
3 way
PO Qty – 100

Receipt Qty 80
Invoice Qty 80
4 way
PO Qty – 100
Receipt Qty 80
Inspection Qty 50
Invoice Qty 50

At the time of creation Purchase Order in the shipments, release we will specify the Match approval option.
Two way matching is nothing but company purchase order quantity, price with Invoice price.
Three way matching is nothing but comparing 3 documents Purchase Order, Receipt and Invoice.
Four ways Matching is nothing but company PO receipt inspection and Invoice documents.

Receipt Documents

Standard - 3 Way
Direct Delivery - 2 way
Inspection Required - 4 way

It is one of the purchasing document will be created while receiving the materials form the suppliers we have 3 types of receipt.
Navigation – Receiving – Receipts and enter.
Create the purchase order and approve it open the receipts from enter PO Number, select find button, system will show the PO Line details, check the check box save to transactions, system write automatically generate Receipt number

Select * from RCV_SHIPMENT_HEADERS

SELECT * FROM RCV_SHIPPMENT_LINES

SELECT * FRO RCV_TRANSACTIONS

SELECT * FORM ORG_ORGANIZATIONS_DEFINITIONS.

Create PO write 3 lines
Go to receipt – generate the receipt for only one Item and either the PO Number again – create another receipt until all the Items over.

Select Receipt_Num
From RCV_shippment_headers
Where shipment_header_id IN (Select shipment_header_id
From rcv_shpment_lines
Where PO_header_id IN( Select Po_Header_ID
From PO_heaer_all
Where segment1=’3452’))
CLASS - 17

MD050 – Functional Design Document

Based on above develop Quotation Report

Query

MASTER QUERY
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 QUOTENO,
PHA.QUOTE_TYPE_LOOKUP_CODE,
TRUNC(PHA.CREATION_DATE) CDATE,
PPF.FULL_NAME BUYER,
H1.LOCATION_CODE SHIPTO,
PHA.QUOTE_VENDOR_QUOTE_NUMBER QUOTEAMT ,
H2.LOCATION_CODE BILLTO ,
PHA.CURRENCY_CODE CURR,
PV.VENDOR_NAME,
PVS.ADDRESS_LINE1,
PVS.CITY,
PVS.ZIP
FROM PO_HEADERS_ALL PHA ,
PER_ALL_PEOPLE_F PPF ,
HR_LOCATIONS H1 ,
HR_LOCATIONS H2,
PO_VENDOR_SITES_ALL PVS,
PO_VENDORS PV
WHERE TYPE_LOOKUP_CODE = 'QUOTATION'
AND PHA.VENDOR_ID = :P_VENDOR_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.SHIP_TO_LOCATION_ID = H1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = H2.LOCATION_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID

LINE – QUERY

SELECT PLA.PO_HEADER_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION ITEMDESC,
MC.SEGMENT1','MC.SEGMENT2 CATEGORY,
PLA.UNIT_PRICE PRICE
FROM PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLA.ORG_ID = MSI.ORGANIZATION_ID
AND PLA.CATEGORY_ID = MC.CATEGORY_ID

Purchase Order Interview Questions.

1) What is the Flow of Purchasing Module
2) Where the types of Requisition and table
3) Types of Purchase orders and tables
4) What is the different between standard PO and Blanket PO
5) We have created a Item but which is not available in the pO what will be the problem
6) What are the types of Match approvals
7) What are the Receipt types
8) When we enter a Blanket Release data where it will be stored
9) If give the requisition no how to find out corresponding PO Numbers
10) If give the PO Number how to find out corresponding Receipt Numbers
11) What is Auto Create and Advantage
12) In which table we can find out
i) Shipped Quantity
ii) Received Quantity
iii) Canceled Quantity
13) Can we create Purchase order directly without
i) Requisition,
ii) RFQ
iii) Quotation
14) Can we have the Receipt without Purchase order
15) What are the tables for RFQ, Quotation, Purchase Order

ORACLE EBS / ORACLE APPS ALERTS

ORACLE EBS / ORACLE APPS ALERTS

What are Oracle Alerts?
A: Oracle Alerts monitor your Database information and notify you when the condition that you have specified is found. You can define Alerts in any Oracle application or custom Oracle application. Some applications (Purchasing, for example) supply Alerts that Can simply be activated and used. There are two type of Alerts, Event and Periodic.

Event Based Alerts : These Alerts are fired/triggered based on some change in data in the database.

Ex: If u want to notify your manager when you create an item in the inventory you can use event based alerts. When you create an item in the inventory it will cretae a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert.In same alert you can also send the information related to that particular item

Periodic Alerts : These Alerts are triggered hourly,daily, weekly, monthly or yearly based on your input.

Ex: If you want to know list of items created on that day at the end of day you can use periodic alerts repeating periodically by single day.This alert is not based on any chages to database.this alert will notify you everyday regardless of data exists or not that means even if no items are created you wil get a blank notification.

Q: What types of actions can be generated when an Alert is triggered?
A: When an alert is triggered or the event is true, the alert can Email a Message, Submit a concurrent program request, Run a SQL statement Script or Run an operating system script.

Q: Can I build an Alert to run with my custom applications or tables?
A: Event or Periodic Alerts can work with any custom application, as long as the application is properly registered within the Oracle Applications package.

Q:  Which Email packages work with Alerts?
A: Oracle Alert is designed to work with Oracle Office, Oracle Interoffice, UNIX Send mail, and VMS Mail.

Q:  Can Alerts be triggered by other Tools? (i.e. other than Oracle Forms and concurrent programs)
A: Oracle Alerts can only be triggered from an application that has been registered in Oracle Applications. Alerts cannot be triggered via SQL updates or deletes to an Alert activated trigger.

Q:  What is Response Processing?
A: Response processing is a component of Alerts which allows the recipients of an alert to reply with a message and have the applications take some action based on the response. Response Processing only works with Oracle Mail Products.

Q:  Do I need Oracle Applications to use Alerts?
Answer——No. The following are the only components required to use Oracle Alerts. The components must be certified versions for your hardware platform and operating system.

-RDBMS
- SQL*PLUS
- FORMS
- ORACLE MAIL product (i.e. Oracle Office or InterOffice

Q: How to create an Alert?

A:

1.Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
2. If you are going for periodic alert decide the frequency.
3. If you have chosen event based alert then find out on what event(insert,update) you want to fire the alert.
4. Decide what data need to be included in the alert.
5. Based on the data you want in the alert write a SELECT SQL statement to pull the data.
6. Create a distribution list grouping all the people to whom you want to send the alert.

Creating and Testing a Event Alert in Oracle EBS / Apps

In following example I will create an Alert which will send email to  specific email ID when ever any body update or create new employee in Employee Master.

Defining an Alert :

1.  Responsibility  –> Alert Manager.

2.  Naviate to the Alerts Window.

Alert –> Define

3.  Define a Period Alert:

Application = Human Resources

Name =  xxscc_test1

Enable = Selected.

4.    Event

Application  = Human Resources

Table  = PER_ALL_PEOPLE_F

5.   Enter the following SQL statement.

select employee_number

into &emp_numfrom

per_all_people_f where rowid=:rowid





6. Verify, to check whether the syntax written is correct.

7. Save

8. Define Alert Actions:

Action Name :  send_email_notification






8. Define Action Details:

Click on Action Details Button

TO :   Give you email ID here.      Note : You can also put here also bind variable with &email if that taken in select statement.

Subject :  Give Subject of you Email.

Text :  hello this is test email &emp_num





9- Define Action Sets

Click on Action Sets Button.

Action Set Name =  Send Email Test

Go to Action Set Details –> Members.

Action =  send_email_notification  as shown bellow.






10- Define Operating Unit

Click on Alert Details Button from first Alert Window.

Navigate to Installation Tab.

Operating Unit = <Your Operating Unit> for me  it is  FS_OU






11- Save.

Now if you Update or Create New Employee you will received notification in given email ID.

AR Receipt Queries

AR Receipt Queries 




All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Total On Account Receipt Amount
SELECT NVL (SUM (ps.amount_due_remaining), 0) total_onacct_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipts_all acr,
ar_cash_receipt_history_all crh,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND ps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'PMT'
AND ps.cash_receipt_id = arr.cash_receipt_id
AND arr.status IN ('ACC')
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id HAVING NVL (SUM (arr.amount_applied), 0) > 0;

-- Query for Total Unapplied Receipt Amount

SELECT NVL (SUM (arr.amount_applied), 0) total_unapp_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipts_all acr,
ar_cash_receipt_history_all crh,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND ps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'PMT'
AND ps.cash_receipt_id = arr.cash_receipt_id
AND arr.status = 'UNAPP'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
HAVING NVL (SUM (arr.amount_applied), 0) > 0;

-- Query for Total Uncleared Receipt Amount

SELECT NVL (SUM (ps.amount_due_remaining), 0) total_uncleared_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all acr,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND ps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'PMT'
AND ps.cash_receipt_id = arr.cash_receipt_id
AND arr.status = 'UNAPP'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND ps.cash_receipt_id = crh.cash_receipt_id
AND crh.status NOT IN ('CLEARED')
HAVING NVL (SUM (arr.amount_applied), 0) > 0;

AR Balance Query

AR Balance Query 



All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Customer Transaction Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS NOT IN ('CM', 'PMT')
AND site_uses.site_use_code = 'BILL_TO'
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'

-- Query for Credit Memo Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'

AP - PO Link Queries

AP - PO Link Queries
=======================================




-- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'



-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'



-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'



-- VENDOR, PO, INVOICE AND PAYMENT DETAILS


SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME

FND_PROFILE and FND_GLOBAL values

FND_PROFILE and FND_GLOBAL values
Posted by Aopu Mohsin

[For more information, please visit http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462356.htm]

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
   
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);