Oracle HRMS Protected Descriptive Flexfields
In oracle HRMS the following DFF's not allows you to change the Definitions. These are used by product Development to deliver std functionality for HRMS.
Further Element information Pay_Element_types_f
Further Assignment Information Per_Assignment_Extra_info
Further Payment Method information Pay_org_payment_methods_f
Further Person Information Per_people_f
Further Job Information Per_jobs
Org Development DF Hr_organization_Information
Sunday, April 26, 2009
Friday, April 24, 2009
Execution Sequences of sql clauses.
Execution Sequences of sql clauses.
a)Select…..
b)Group by…
c)Having…
d)Orderby..
a)Select…..
b)Group by…
c)Having…
d)Orderby..
Diff Between Implicit and Explicit Cursors
Diff Between Implicit and Explicit Cursors
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.
2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.
2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block
Flex mode and Confine mode
Flex mode and Confine mode
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.
BIND Vs LEXICAL
BIND Vs LEXICAL
BIND VARIABLE :
-- are used to replace a single value in sql, pl/sql
-- bind variable may be used to replace expressions in select, where, group, order
by, having, connect by, start with cause of queries.
-- bind reference may not be referenced in FROM clause (or) in place of
reserved words or clauses.
LEXICAL REFERENCE:
-- you can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with.
-- you can’t make lexical reference in a pl/sql statmetns.
BIND VARIABLE :
-- are used to replace a single value in sql, pl/sql
-- bind variable may be used to replace expressions in select, where, group, order
by, having, connect by, start with cause of queries.
-- bind reference may not be referenced in FROM clause (or) in place of
reserved words or clauses.
LEXICAL REFERENCE:
-- you can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with.
-- you can’t make lexical reference in a pl/sql statmetns.
Types of Reports in APPS
Types of Reports in APPS
Oracle Reports(D2k 6i): This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.
Oracle Discoverer:This is an intuitive tool for creating reports and performing on-line analysis.The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel
Oracle XML Publisher: This is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report, XML Publisher merges the custom templates with the concurrentrequest extracts data to generate output in RTF, PDF, HTML and EXCEL.
Business Intelligence System (BI): This tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.
This tools to provide high level information to run their business such as the profitability of a particular business unit.
FSG Reports (Financial Statement Generator): Very powerful report building tool for Oracle General Ledger. Benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only Disadvantage of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.
RXi Report:(Variable reports) :With this tool a user has the ability to print the same report with multiple layouts.This tool is most used on Oracle Financials Applications>
Oracle Reports(D2k 6i): This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.
Oracle Discoverer:This is an intuitive tool for creating reports and performing on-line analysis.The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel
Oracle XML Publisher: This is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report, XML Publisher merges the custom templates with the concurrentrequest extracts data to generate output in RTF, PDF, HTML and EXCEL.
Business Intelligence System (BI): This tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.
This tools to provide high level information to run their business such as the profitability of a particular business unit.
FSG Reports (Financial Statement Generator): Very powerful report building tool for Oracle General Ledger. Benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only Disadvantage of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.
RXi Report:(Variable reports) :With this tool a user has the ability to print the same report with multiple layouts.This tool is most used on Oracle Financials Applications>
FNDLOAD
FNDLOAD
when we are working in oracle application development/implementation project? The equally important AOL data Migration takes place necessary to synchronize the data across databases instance during installation and upgarde.
Using FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.The Conversion between database format and text file format is specified by a configuration file.
What can be Done?
·It can be done following list
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Concurrent Manager Schedules
Advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. No learning curve. this is relief for developer/dbas
3. Fully supported and recommended by Oracle
4. Capture the migrations in a file and use it during installations(log file).
5. Pin-point when something happened and where (database) easily
6. AOL data migration process is now simplified!
Disadvantages
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. No validation against migrating database/instance sensitive data
The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]
· The mode is either DOWNLOAD or UPLOAD.
· The configfile is the file that Fndload needs to download on upload data.
· T he data file is the output file, in which the downloaded data is written
· The entity is the entity you want to download,
·
Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
Where is Config File Located
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
·Data files with extension .ldt
· The configfiles (.lct) are delivered and maintained by Oracle
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME
oDownloading a parent automatically downloads all children - (Example) Concurrent Program download
Sample Script Code for these Objects :
1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name
2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=prod LOOKUP_TYPE=lookup name
3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=prod DESCRIPTIVE_FLEXFIELD_NAME=desc flex name P_CONTEXT_CODE=context name
4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=prod ID_FLEX_CODE=key flex code P_STRUCTURE_CODE=structure name
5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=prod CONCURRENT_PROGRAM_NAME=concurrent name
6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=value set name
7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=value set name
8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=profile option APPLICATION_SHORT_NAME=prod
9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=request group APPLICATION_SHORT_NAME=prod
10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=prod REQUEST_SET_NAME=request set
11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=responsibility
12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=menu_name
13 Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt
14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []
when we are working in oracle application development/implementation project? The equally important AOL data Migration takes place necessary to synchronize the data across databases instance during installation and upgarde.
Using FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.The Conversion between database format and text file format is specified by a configuration file.
What can be Done?
·It can be done following list
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Concurrent Manager Schedules
Advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. No learning curve. this is relief for developer/dbas
3. Fully supported and recommended by Oracle
4. Capture the migrations in a file and use it during installations(log file).
5. Pin-point when something happened and where (database) easily
6. AOL data migration process is now simplified!
Disadvantages
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. No validation against migrating database/instance sensitive data
The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]
· The mode is either DOWNLOAD or UPLOAD.
· The configfile is the file that Fndload needs to download on upload data.
· T he data file is the output file, in which the downloaded data is written
· The entity is the entity you want to download,
·
Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
Where is Config File Located
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
·Data files with extension .ldt
· The configfiles (.lct) are delivered and maintained by Oracle
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME
oDownloading a parent automatically downloads all children - (Example) Concurrent Program download
Sample Script Code for these Objects :
1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name
2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=prod LOOKUP_TYPE=lookup name
3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=prod DESCRIPTIVE_FLEXFIELD_NAME=desc flex name P_CONTEXT_CODE=context name
4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=prod ID_FLEX_CODE=key flex code P_STRUCTURE_CODE=structure name
5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=prod CONCURRENT_PROGRAM_NAME=concurrent name
6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=value set name
7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=value set name
8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=profile option APPLICATION_SHORT_NAME=prod
9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=request group APPLICATION_SHORT_NAME=prod
10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=prod REQUEST_SET_NAME=request set
11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=responsibility
12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=menu_name
13 Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt
14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []
How to Unlock the Objects
How to Unlock the Objects
The following query could be useful :
Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))
Get the Process id from the query. Login as unix user and run the following command to kill the process.
$kill -9 SPID
The following query could be useful :
Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))
Get the Process id from the query. Login as unix user and run the following command to kill the process.
$kill -9 SPID
Locate Trace file and Enviroment files from Front End
Locate Trace file and Enviroment files from Front End
After completing traceinge, You can find the Trace file location path by using following methods
Method 1) select value from v$parameter
where name ='user_dump_dest'
The above Query returns Trace file location Path.
Method 2) Login As sysadmin
Click at Goto ->OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Databse Init.ora Setting.
Here You can find out Parameter name as user_dump_dest , utl_file_dir and corresponding values.
After completing traceinge, You can find the Trace file location path by using following methods
Method 1) select value from v$parameter
where name ='user_dump_dest'
The above Query returns Trace file location Path.
Method 2) Login As sysadmin
Click at Goto ->OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Databse Init.ora Setting.
Here You can find out Parameter name as user_dump_dest , utl_file_dir and corresponding values.
Concurrent Program output in MS Excel format
Concurrent Program output in MS Excel format
1)Login as sysadmin
2) switch to Sysadmin responsibility
3)go to Install->Viewer Options
4) Add entries as Below
File Format =Text Mime
Type=application/vnd.ms-excel
Description=Microsoft Excel
5) save the changes
6) Run any concurrent program
7) Goto-> tools and Copy file
Your able to see the output into Excel format
1)Login as sysadmin
2) switch to Sysadmin responsibility
3)go to Install->Viewer Options
4) Add entries as Below
File Format =Text Mime
Type=application/vnd.ms-excel
Description=Microsoft Excel
5) save the changes
6) Run any concurrent program
7) Goto-> tools and Copy file
Your able to see the output into Excel format
Diffierent types Apps Tables
Diffierent types Apps Tables
_ALL Conatains multi org data before using this table you need to set client_into to specific org , Org_id is common column.
_V view created on base table
_VL view created on multi language tables.
always use the condition LANGUAGE = USERENV(’LANG’).
_TL Tables support multi language.
_B These Main base tables
_F these date tracked tables for only in HRMS and these table contain 2 common columns effective_start_date and effective_end_date
_S sequence related tables
_AVN Audit view shadow tables contains data track information
_ALL Conatains multi org data before using this table you need to set client_into to specific org , Org_id is common column.
_V view created on base table
_VL view created on multi language tables.
always use the condition LANGUAGE = USERENV(’LANG’).
_TL Tables support multi language.
_B These Main base tables
_F these date tracked tables for only in HRMS and these table contain 2 common columns effective_start_date and effective_end_date
_S sequence related tables
_AVN Audit view shadow tables contains data track information
OM Flow and table level Information
OM Flow and table level Information
Steps in Order Cycle:
1) Order Entry
2) Booking
3) Pick release :
For this we have to go to
Shipping Responsibilty Release sales order
Here In this form , In the ORDER tab, we have to enter ORDER Number
And delete the Scheduled shipped Dates To & Requested Dates To.
In SHIPPING tab, set AUTO CREATE DELIVERY to YES. In INVENTORY tab enter WAREHOUSE, set AUTO ALLOCATE to YES and AUTO PICK CONFIRM to YES. IF we set AUTO PICK CONFIRM to NO, then We have to go for the following steps
1. go to Inventory Resp
Move order à Transact Move Order then it will ask for
warehouse information. Give the same name as before [M2]
In this form, In the HEADER tab, enter the BATCH
NUMBER of the order that is picked .Then Click FIND
Button. Click on VIEW/UPDATE Allocation, then
Click TRANSACT button. Then Transact button will be
deactivated then just close it and go to next step.
4) Shipping :
For this we need to go to Shipping Transaction Give the order Number, and click find
Then we can see the order status.
Then we have to click DELIVERY Tab Button, in the Action LOV
We have to choose, SHIP CONFIRM.
Then four concurrent program will run in the background.
Such As::
1.) INTERFACE TRIP Stop
2.) Commercial Invoice
3.) Packing Slip Report
4.) Bill of Lading
After this concurrent program will complete successfully, we have to run
One more WORKFLOW BACKGROUND PROGRAM.
· If we don’t want to ship all the items, that are PICKED, then we have to click LINE/LPN tab , then click DETAIL button .
Now, in that form , in the SHIPPING field, we have to enter how Much quantity of items, we want to ship . The rest remain quantity, that are Ordered will become backorder quantity .
5) Interfacing with AR :
After WORKFLOW BACKGROUND PROGRAM
Concurrent program will complete successfully, we have to run
AUTO INVOICE MASTER PROGRAM from
RECEIVABLE RESPONSIBILTY. After this program
will complete successfully , we can the invoice details in
RECEIVALE à TRANSACTIONS à TRANSACTIONS. Here in
This Form, we have to give our order number in reference field
And query for the invoice details .Then we can see the invoice details.
Table Level Information:
==========================
Order Entry
• At the header level a record gets inserted into the header table
OE_ORDER_HEADERS_ALL.
• At the line level, record(s) get inserted into the Line table
OE_ORDER_LINES_ALL.
Order Booking
• This will update FLOW_STATUS_CODE value in the table
OE_ORDER_HEADERS_ALL to “BOOKED”
• The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will change to
AWAITING_SHIPPING.
• Record(s) will be created into the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
OE_INTERFACED_FLAG=’N’ (Not interfaced to OM)
INV_INTERFACED_FLAG=’N’ (Not interfaced to Inv)
• Record(s) will be created into WSH_DELIVERY_ASSIGNMENTS but with
DELIVERY_ID null.
Pick Release
------------------
IF “Autocreate Delivery” option = “Yes” THEN
• ) Create a record into the table WSH_NEW_DELIVERIES
• ) Update WSH_DELIVERY_ASSIGNMENTS with DELIVERY_ID, thus
• ) Update WSH_DELIVERY_DETAILS with RELEASED_STATUS=’Y
Auto Invoicing
----------------------
Before running “Autoinvoice Program”, record(s) will exist into the table
RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
INTERFACE_LINE_ATTRIBUTE3 = &Delivery_id
SALES_ORDER = &Order_number
After running the “Auto invoice Program” for the order:
Records will be deleted from the table RA_INTERFACE_LINES_ALL and new details will be created into the following RA transaction tables.
>RA_CUSTOMER_TRX_ALL with
INTERFACE_HEADER_ATTRIBUTE1=&Order_number
RA_CUSTOMER_TRX_LINES_ALL with
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
SALES_ORDER = &Order_number
Steps in Order Cycle:
1) Order Entry
2) Booking
3) Pick release :
For this we have to go to
Shipping Responsibilty Release sales order
Here In this form , In the ORDER tab, we have to enter ORDER Number
And delete the Scheduled shipped Dates To & Requested Dates To.
In SHIPPING tab, set AUTO CREATE DELIVERY to YES. In INVENTORY tab enter WAREHOUSE, set AUTO ALLOCATE to YES and AUTO PICK CONFIRM to YES. IF we set AUTO PICK CONFIRM to NO, then We have to go for the following steps
1. go to Inventory Resp
Move order à Transact Move Order then it will ask for
warehouse information. Give the same name as before [M2]
In this form, In the HEADER tab, enter the BATCH
NUMBER of the order that is picked .Then Click FIND
Button. Click on VIEW/UPDATE Allocation, then
Click TRANSACT button. Then Transact button will be
deactivated then just close it and go to next step.
4) Shipping :
For this we need to go to Shipping Transaction Give the order Number, and click find
Then we can see the order status.
Then we have to click DELIVERY Tab Button, in the Action LOV
We have to choose, SHIP CONFIRM.
Then four concurrent program will run in the background.
Such As::
1.) INTERFACE TRIP Stop
2.) Commercial Invoice
3.) Packing Slip Report
4.) Bill of Lading
After this concurrent program will complete successfully, we have to run
One more WORKFLOW BACKGROUND PROGRAM.
· If we don’t want to ship all the items, that are PICKED, then we have to click LINE/LPN tab , then click DETAIL button .
Now, in that form , in the SHIPPING field, we have to enter how Much quantity of items, we want to ship . The rest remain quantity, that are Ordered will become backorder quantity .
5) Interfacing with AR :
After WORKFLOW BACKGROUND PROGRAM
Concurrent program will complete successfully, we have to run
AUTO INVOICE MASTER PROGRAM from
RECEIVABLE RESPONSIBILTY. After this program
will complete successfully , we can the invoice details in
RECEIVALE à TRANSACTIONS à TRANSACTIONS. Here in
This Form, we have to give our order number in reference field
And query for the invoice details .Then we can see the invoice details.
Table Level Information:
==========================
Order Entry
• At the header level a record gets inserted into the header table
OE_ORDER_HEADERS_ALL.
• At the line level, record(s) get inserted into the Line table
OE_ORDER_LINES_ALL.
Order Booking
• This will update FLOW_STATUS_CODE value in the table
OE_ORDER_HEADERS_ALL to “BOOKED”
• The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will change to
AWAITING_SHIPPING.
• Record(s) will be created into the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
OE_INTERFACED_FLAG=’N’ (Not interfaced to OM)
INV_INTERFACED_FLAG=’N’ (Not interfaced to Inv)
• Record(s) will be created into WSH_DELIVERY_ASSIGNMENTS but with
DELIVERY_ID null.
Pick Release
------------------
IF “Autocreate Delivery” option = “Yes” THEN
• ) Create a record into the table WSH_NEW_DELIVERIES
• ) Update WSH_DELIVERY_ASSIGNMENTS with DELIVERY_ID, thus
• ) Update WSH_DELIVERY_DETAILS with RELEASED_STATUS=’Y
Auto Invoicing
----------------------
Before running “Autoinvoice Program”, record(s) will exist into the table
RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
INTERFACE_LINE_ATTRIBUTE3 = &Delivery_id
SALES_ORDER = &Order_number
After running the “Auto invoice Program” for the order:
Records will be deleted from the table RA_INTERFACE_LINES_ALL and new details will be created into the following RA transaction tables.
>RA_CUSTOMER_TRX_ALL with
INTERFACE_HEADER_ATTRIBUTE1=&Order_number
RA_CUSTOMER_TRX_LINES_ALL with
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
SALES_ORDER = &Order_number
Oracle Purchasing Terminology
Oracle Purchasing Terminology
Terms used in the conventional purchasing system and as referred in Oracle Purchasing may be different. A brief explanation of the ‘Oracle Purchasing’ terms vis-à-vis the existing terminology is provided the following paragraphs. These terms are extensively used in documenting the ‘Purchasing - To Be’ flows and it is recommended that the various users of this system get acquainted with the same.
Requestor: Any Employee who requires specific items to meet his operational needs. Requestor may or may not have the system access to enter the on-line requisitions. Requestor with no computer access will give their requisitions manually on pre-printed requisition form to the requisition ‘Preparer’ within the concerned department.
Preparer: Employee in a department with the responsibility of entering Purchase Requisitions in the system.
Approver: Employee with the responsibility and authority of approving the requisition and / or the Quotation Analysis Document and / or the Purchase Orders.
Buyer: Employee in the Purchase Department with the responsibility of entering the Requests for Quotation, the Quotations received from the Supplier and the Purchase Orders in the new system.
Request For Quotation (RFQ): Synonym for ‘Inquiry’. RFQs are sent to the vendors, requesting them for their quotations for the items / services required to be purchased.
Quotation: Quotations are the ‘Vendor Offers’ for the required items / services.
Quotation Analysis: It is a ‘Comparative Statement of Quotations’.
Standard Purchase Order: Standard Purchase Orders are created for purchase of various items / services where details such as the quantity to be purchased and the negotiated costs.
Blanket Purchase Agreement: Blanket purchase agreements are signed with the vendor for the supply of goods / services over a period. Such agreements can be used when details of the goods / services you plan to buy from a specific supplier in a period are known, but you do not yet know the details of your delivery schedules. They can also be used to specify negotiated prices for your items before actually buying them.
Blanket Release: You can issue a Blanket Release against a Blanket Purchase Agreement to place the actual order with the vendor. The Releases should be within the Blanket Agreement effectivity dates.
Planned Purchase Order: It is a long term agreement committing to buy items / services from a single source. The tentative delivery schedule is given to the vendor along with other details like quantities and estimated costs.
Schedule Release: This document is issued to the vendor against a Planned Purchase Order to place the actual order. This document will confirm the specific quantity and delivery date to the vendor.
Contract Purchase Agreements: Contract Purchase Agreements may be entered into with suppliers to agree on specific terms and conditions without indicating the goods or services that will actually be purchased. Standard Purchase Orders can be issued at a later date referencing such Contracts.
Purchasing Cycle:
Requisitions: Requisitions generated by various departments of your organization. They basically say that ‘I WANT THIS ITEM’ mentioning the item quantity and required date
Request for Quotation: The purchasing person sends the requirement to various suppliers giving details of the item and can also specify own terms and conditions such as payment terms, price etc.
Quotations: The Supplier responds to the RFQ and sends their quotations mentioning details of the item and all their terms and conditions regarding payment terms, discount, delivery schedules, etc.
Purchase Orders: The quotations received from various suppliers are compared and PO made for them in which all the terms and conditions of the firm are mentioned as regards to Payment, price, etc.
Receiving Goods: Suppliers ship the goods, which are received in your organization
Payments: After clearance from the receiving department about the receipt of goods, payments are made to the suppliers.
Close the PO: After the items are received, payments made you can close the PO.
Purchasing Requirements
· Item
· Quantity
· Ship to Address
· Bill to Address
· Delivery Schedules
· Terms and Conditions
· PO Numbering
· Approval Authority
· Supplier
· Accounts
Terms used in the conventional purchasing system and as referred in Oracle Purchasing may be different. A brief explanation of the ‘Oracle Purchasing’ terms vis-à-vis the existing terminology is provided the following paragraphs. These terms are extensively used in documenting the ‘Purchasing - To Be’ flows and it is recommended that the various users of this system get acquainted with the same.
Requestor: Any Employee who requires specific items to meet his operational needs. Requestor may or may not have the system access to enter the on-line requisitions. Requestor with no computer access will give their requisitions manually on pre-printed requisition form to the requisition ‘Preparer’ within the concerned department.
Preparer: Employee in a department with the responsibility of entering Purchase Requisitions in the system.
Approver: Employee with the responsibility and authority of approving the requisition and / or the Quotation Analysis Document and / or the Purchase Orders.
Buyer: Employee in the Purchase Department with the responsibility of entering the Requests for Quotation, the Quotations received from the Supplier and the Purchase Orders in the new system.
Request For Quotation (RFQ): Synonym for ‘Inquiry’. RFQs are sent to the vendors, requesting them for their quotations for the items / services required to be purchased.
Quotation: Quotations are the ‘Vendor Offers’ for the required items / services.
Quotation Analysis: It is a ‘Comparative Statement of Quotations’.
Standard Purchase Order: Standard Purchase Orders are created for purchase of various items / services where details such as the quantity to be purchased and the negotiated costs.
Blanket Purchase Agreement: Blanket purchase agreements are signed with the vendor for the supply of goods / services over a period. Such agreements can be used when details of the goods / services you plan to buy from a specific supplier in a period are known, but you do not yet know the details of your delivery schedules. They can also be used to specify negotiated prices for your items before actually buying them.
Blanket Release: You can issue a Blanket Release against a Blanket Purchase Agreement to place the actual order with the vendor. The Releases should be within the Blanket Agreement effectivity dates.
Planned Purchase Order: It is a long term agreement committing to buy items / services from a single source. The tentative delivery schedule is given to the vendor along with other details like quantities and estimated costs.
Schedule Release: This document is issued to the vendor against a Planned Purchase Order to place the actual order. This document will confirm the specific quantity and delivery date to the vendor.
Contract Purchase Agreements: Contract Purchase Agreements may be entered into with suppliers to agree on specific terms and conditions without indicating the goods or services that will actually be purchased. Standard Purchase Orders can be issued at a later date referencing such Contracts.
Purchasing Cycle:
Requisitions: Requisitions generated by various departments of your organization. They basically say that ‘I WANT THIS ITEM’ mentioning the item quantity and required date
Request for Quotation: The purchasing person sends the requirement to various suppliers giving details of the item and can also specify own terms and conditions such as payment terms, price etc.
Quotations: The Supplier responds to the RFQ and sends their quotations mentioning details of the item and all their terms and conditions regarding payment terms, discount, delivery schedules, etc.
Purchase Orders: The quotations received from various suppliers are compared and PO made for them in which all the terms and conditions of the firm are mentioned as regards to Payment, price, etc.
Receiving Goods: Suppliers ship the goods, which are received in your organization
Payments: After clearance from the receiving department about the receipt of goods, payments are made to the suppliers.
Close the PO: After the items are received, payments made you can close the PO.
Purchasing Requirements
· Item
· Quantity
· Ship to Address
· Bill to Address
· Delivery Schedules
· Terms and Conditions
· PO Numbering
· Approval Authority
· Supplier
· Accounts
Wednesday, April 22, 2009
How to find Descriptive FlexFileds Segments defined for paticular Application
How to find Descriptive FlexFileds Segments defined for paticular Application
For Example if you want to know the Descriptive Flexfields defined for your Purchase Requisition Header level form,
Go to System Adminstrator Responsibility--->Application--->Flexfield--->Descriptive--->Segments--->
Here you can give F11 and query for Application 'Purchasing' and Title as 'Requisition Headers'
and here you can see how the segments attributes are defined.
For Example if you want to know the Descriptive Flexfields defined for your Purchase Requisition Header level form,
Go to System Adminstrator Responsibility--->Application--->Flexfield--->Descriptive--->Segments--->
Here you can give F11 and query for Application 'Purchasing' and Title as 'Requisition Headers'
and here you can see how the segments attributes are defined.
AR Query to get open invoices for single/All customers
AR Query to get open invoice for single customer /for all customer from the table ar_payment_schedules_all , you can modify the query how you want to get the details
select aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
and hc.cust_account_id=21924 --- Here you can give ths customer for whom you want open invoices to be retrieved
select aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
and hc.cust_account_id=21924 --- Here you can give ths customer for whom you want open invoices to be retrieved
How to Cancel PO Requisition created
Cancellation of PO Requisition which is created
Go to PO Super User responsibility --->Reqisition Summary ---> Here open the Requisition Number which you want to cancel and Go to Menu Bar TOOLS Option--->Control and Mention Proper reason and Cancel the Requisition.
Once the Requisition is cancelled it will also Cancel the workflow and the status of workflow will be in Canclled mode.
Go to PO Super User responsibility --->Reqisition Summary ---> Here open the Requisition Number which you want to cancel and Go to Menu Bar TOOLS Option--->Control and Mention Proper reason and Cancel the Requisition.
Once the Requisition is cancelled it will also Cancel the workflow and the status of workflow will be in Canclled mode.
Tuesday, April 21, 2009
How To Identify All Requisitions And Purchase Orders With Errored Activities
The following scripts have been provided to return requisitions and purchase orders with erroredactivities:
SELECT prh.segment1, prh.org_id, prh.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stack
FROM po_requisition_headers_all prh,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'REQAPPRV'
AND ias.item_key = prh.wf_item_key
AND ias.item_type = prh.wf_item_type
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'REQAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY prh.segment1, ias.execution_time;
--- Purchase Orders
SELECT pha.segment1, pha.org_id, pha.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stackfrom
FROM po_headers_all pha,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'POAPPRV'
AND ias.item_key = pha.wf_item_key
AND ias.item_type = pha.wf_item_type
AND pha.authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'POAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY pha.segment1, ias.execution_time;
SELECT prh.segment1, prh.org_id, prh.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stack
FROM po_requisition_headers_all prh,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'REQAPPRV'
AND ias.item_key = prh.wf_item_key
AND ias.item_type = prh.wf_item_type
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'REQAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY prh.segment1, ias.execution_time;
--- Purchase Orders
SELECT pha.segment1, pha.org_id, pha.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stackfrom
FROM po_headers_all pha,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'POAPPRV'
AND ias.item_key = pha.wf_item_key
AND ias.item_type = pha.wf_item_type
AND pha.authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'POAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY pha.segment1, ias.execution_time;
Monday, April 20, 2009
Auto Invoice Script
--Just replace the constant values with variables and call this procedure in a valid cursor loop
CREATE OR REPLACE PROCEDURE inv_proc_v (
asqlcode OUT NUMBER,
asqlerrm OUT VARCHAR2
)
IS
v_batch_source_name VARCHAR2 (100);
v_currency_code VARCHAR2 (10);
v_cust_trx_type_id NUMBER;
v_term_id NUMBER;
BEGIN
BEGIN
SELECT NAME
INTO v_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = 1228 AND org_id = 204;
END;
BEGIN
SELECT currency_code
INTO v_currency_code
FROM fnd_currencies
WHERE issuing_territory_code = 'US' AND enabled_flag = 'Y';
END;
BEGIN
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = 'Invoice' AND org_id = 204;
END;
BEGIN
SELECT term_id
INTO v_term_id
FROM ra_terms_tl
WHERE NAME = '30 Net';
END;
BEGIN
SELECT set_of_books_id
INTO v_sob_id
FROM gl.gl_sets_of_books
WHERE short_name = 'Vision Operations';
END;
BEGIN
INSERT INTO ra_interface_lines_all
(interface_line_id, interface_line_context,
--ra_batch_sources_all
interface_line_attribute1, interface_line_attribute2,
batch_source_name, --ra_batch_sources_all
set_of_books_id, --gl.gl_setsof_books
line_type, tax_code,
--FND_lookups.tax_type
description, --mtl_system_items
currency_code, --fnd_currencies
amount, cust_trx_type_id,
--ra_cust_trx_types_all
term_id, --ar_terms
conversion_type,
--GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE
conversion_rate, trx_date,
gl_date, quantity, quantity_ordered, unit_selling_price,
unit_standard_price, inventory_item_id, --MTL_SYSTEM_ITEMS
territory_id,
--ra_territories
uom_code, --MTL_UNITS_OF_MEASURE
created_by, --fnd_global.user_id
creation_date, last_update_date,
last_updated_by, org_id, --fnd_global.org_id
tax_exempt_flag,
--FND_lookups.tax_control_flag
orig_system_bill_customer_id, --hz_customer_accounts
orig_system_bill_address_id, --hz_cust_sites_USES_all
orig_system_sold_customer_id
) --hz_customer_accounts
VALUES (ra_customer_trx_lines_s.NEXTVAL, 'LEGACY',
'1101233', '11045',
'LEGACY', 1, 'LINE', 'Exempt',
'Paper Carrier', 'USD', '1000.00', 1,
1060, 'User', 1, SYSDATE,
SYSDATE, 12, 12, 50.00,
50.00, 6074, 1003,
'Ea', 1318, SYSDATE, SYSDATE,
1318, 204, 'S',
5789,
5808, 5789
);
END;
END inv_proc_v;
/
CREATE OR REPLACE PROCEDURE inv_proc_v (
asqlcode OUT NUMBER,
asqlerrm OUT VARCHAR2
)
IS
v_batch_source_name VARCHAR2 (100);
v_currency_code VARCHAR2 (10);
v_cust_trx_type_id NUMBER;
v_term_id NUMBER;
BEGIN
BEGIN
SELECT NAME
INTO v_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = 1228 AND org_id = 204;
END;
BEGIN
SELECT currency_code
INTO v_currency_code
FROM fnd_currencies
WHERE issuing_territory_code = 'US' AND enabled_flag = 'Y';
END;
BEGIN
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = 'Invoice' AND org_id = 204;
END;
BEGIN
SELECT term_id
INTO v_term_id
FROM ra_terms_tl
WHERE NAME = '30 Net';
END;
BEGIN
SELECT set_of_books_id
INTO v_sob_id
FROM gl.gl_sets_of_books
WHERE short_name = 'Vision Operations';
END;
BEGIN
INSERT INTO ra_interface_lines_all
(interface_line_id, interface_line_context,
--ra_batch_sources_all
interface_line_attribute1, interface_line_attribute2,
batch_source_name, --ra_batch_sources_all
set_of_books_id, --gl.gl_setsof_books
line_type, tax_code,
--FND_lookups.tax_type
description, --mtl_system_items
currency_code, --fnd_currencies
amount, cust_trx_type_id,
--ra_cust_trx_types_all
term_id, --ar_terms
conversion_type,
--GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE
conversion_rate, trx_date,
gl_date, quantity, quantity_ordered, unit_selling_price,
unit_standard_price, inventory_item_id, --MTL_SYSTEM_ITEMS
territory_id,
--ra_territories
uom_code, --MTL_UNITS_OF_MEASURE
created_by, --fnd_global.user_id
creation_date, last_update_date,
last_updated_by, org_id, --fnd_global.org_id
tax_exempt_flag,
--FND_lookups.tax_control_flag
orig_system_bill_customer_id, --hz_customer_accounts
orig_system_bill_address_id, --hz_cust_sites_USES_all
orig_system_sold_customer_id
) --hz_customer_accounts
VALUES (ra_customer_trx_lines_s.NEXTVAL, 'LEGACY',
'1101233', '11045',
'LEGACY', 1, 'LINE', 'Exempt',
'Paper Carrier', 'USD', '1000.00', 1,
1060, 'User', 1, SYSDATE,
SYSDATE, 12, 12, 50.00,
50.00, 6074, 1003,
'Ea', 1318, SYSDATE, SYSDATE,
1318, 204, 'S',
5789,
5808, 5789
);
END;
END inv_proc_v;
/
Importing Blanket Purchase Aggrements(BPA)
Importing Blanket Purchase Aggrements(BPA)
In this article we will see what a Blanket Purchase Agreement is and how we can import them along with the price breaks.
Overview of Blanket Purchase Agreements:You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
Blanket Releases: You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivity dates. If your purchase agreement has price breaks, the quantity entered on the release determines what break price is defaulted into the Price field.
Import Process: The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders.Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
You need to populate PO_HEADERS_INTERFACE and PO_LINES_INTERFACE to import header and line information into Purchasing. PO_LINES_INTERFACE table contains both line and shipment information, and imports data into both the PO_LINES and PO_LINE_LOCATIONS. The below are the additional columns that are required in PO_LINES_INTERFACE if you want to import price break information:
LINE_NUM
SHIPMENT_NUM
QUANTITY
UNIT_PRIC
If you are importing price break information through catalog quotations, you can also, optionally, populate the following columns in the PO_LINES_INTERFACE table: MIN_ORDER_QUANTITY
MAX_ORDER_QUANTITY
Lets take an example to better understand.Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1)quantity = 500, price = 10, effective date from '01-JAN-2006' to '31-JUN-2006'
2)quantity = 500, price = 11, effective date from '01-JUL-2006' to '01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’ All the line-level records above must have the same INTERFACE_HEADER_ID.
--Inserting Header Information
insert into po_headers_interface
(interface_header_id,
action,
org_id,
document_type_code,
vendor_id,
vendor_site_id,
effective_date,
expiration_date,
Vendor_doc_num)
values
(po_headers_interface_s.nextval,
'ORIGINAL',
204,
'BLANKET',
21,
41,
'01-JAN-2006',
'01-JAN-2007',
'VENDOR04302006');
--Inserting Line Information
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
unit_price,
unit_of_measure,
effective_date,
expiration_date,
ship_to_organization_id,
ship_to_location_id,
PRICE_BREAK_LOOKUP_CODE)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
20,
'Each',
'01-JAN-2006',
'01-JAN-2007',
207,
207,
'NON CUMULATIVE');
Note: Cumulative: Price breaks apply to the cumulative quantity on all release shipments for the item. Non–cumulative: Price breaks apply to quantities on individual release shipments for the item.
--Inserting First Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
1,
'PRICE BREAK',
500,
10,
'Each',
207,
207,
'01-JAN-2006',
'30-JUN-2006');
--Inserting Second Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
2,
'PRICE BREAK',
500,
11,
'Each',
207,
207,
'01-JUL-2006',
'01-JAN-2007');
Final Step:
Run Import Price Catalog Concurrent Program to create this Blanket Purchase Agreement.
In this article we will see what a Blanket Purchase Agreement is and how we can import them along with the price breaks.
Overview of Blanket Purchase Agreements:You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
Blanket Releases: You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivity dates. If your purchase agreement has price breaks, the quantity entered on the release determines what break price is defaulted into the Price field.
Import Process: The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders.Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
You need to populate PO_HEADERS_INTERFACE and PO_LINES_INTERFACE to import header and line information into Purchasing. PO_LINES_INTERFACE table contains both line and shipment information, and imports data into both the PO_LINES and PO_LINE_LOCATIONS. The below are the additional columns that are required in PO_LINES_INTERFACE if you want to import price break information:
LINE_NUM
SHIPMENT_NUM
QUANTITY
UNIT_PRIC
If you are importing price break information through catalog quotations, you can also, optionally, populate the following columns in the PO_LINES_INTERFACE table: MIN_ORDER_QUANTITY
MAX_ORDER_QUANTITY
Lets take an example to better understand.Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1)quantity = 500, price = 10, effective date from '01-JAN-2006' to '31-JUN-2006'
2)quantity = 500, price = 11, effective date from '01-JUL-2006' to '01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’ All the line-level records above must have the same INTERFACE_HEADER_ID.
--Inserting Header Information
insert into po_headers_interface
(interface_header_id,
action,
org_id,
document_type_code,
vendor_id,
vendor_site_id,
effective_date,
expiration_date,
Vendor_doc_num)
values
(po_headers_interface_s.nextval,
'ORIGINAL',
204,
'BLANKET',
21,
41,
'01-JAN-2006',
'01-JAN-2007',
'VENDOR04302006');
--Inserting Line Information
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
unit_price,
unit_of_measure,
effective_date,
expiration_date,
ship_to_organization_id,
ship_to_location_id,
PRICE_BREAK_LOOKUP_CODE)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
20,
'Each',
'01-JAN-2006',
'01-JAN-2007',
207,
207,
'NON CUMULATIVE');
Note: Cumulative: Price breaks apply to the cumulative quantity on all release shipments for the item. Non–cumulative: Price breaks apply to quantities on individual release shipments for the item.
--Inserting First Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
1,
'PRICE BREAK',
500,
10,
'Each',
207,
207,
'01-JAN-2006',
'30-JUN-2006');
--Inserting Second Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
2,
'PRICE BREAK',
500,
11,
'Each',
207,
207,
'01-JUL-2006',
'01-JAN-2007');
Final Step:
Run Import Price Catalog Concurrent Program to create this Blanket Purchase Agreement.
Friday, April 17, 2009
How to assign Approval Limits to user
How to assign Approval Limits to user
PO Super User
Setup > Approvals > Approval Assignments
Select Job = .GCSM Delivery Manager. (User job title)
Document Type = Approve Purchase Requisitions
Approval Group = APPROVAL 5000
Once the above process is done then we should run the process 'i2 AP Maintain Web Signing Limits'. after this process the AP Signing Limits table was automatically updated.
PO Super User
Setup > Approvals > Approval Assignments
Select Job = .GCSM Delivery Manager. (User job title)
Document Type = Approve Purchase Requisitions
Approval Group = APPROVAL 5000
Once the above process is done then we should run the process 'i2 AP Maintain Web Signing Limits'. after this process the AP Signing Limits table was automatically updated.
Subscribe to:
Posts (Atom)