P2P and O2C: Procure to Pay and Order to Cash Cycles

Oracle has developed this ERP solution which truly covers these both cycles as well as many others. Oracle EBS comprises of the Standard Core Business Management

applications like General Ledger, Payables, Receivables, Purchasing, Order Management, Inventory, Discrete Manufacturing, Process Manufacturing , HRMS and many

more. The application mentioned are so integrated that it handles the beginning to end of both Assets and Liabilities. When refering to Assets its referring to applications like

Order Management and Receivables, and when refering to Liabilities its referring to Purchasing and Payables and both of these Assets and Liabilities are finally pushed and

calculated in Oracle General Ledger.

The base or the heart of Oracle EBS is Oracle General Ledger. we can say GL an intrinsic.

Procure to Pay:
Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this

is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.
Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.
Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best

quote.
Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that

supplier
Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in

Oracle Purchasing.

Oracle Inventory / Oracle Assets: It’s up to us whether we want to receive the goods at our head office or Inventory directly. In either case we move the received

goods to different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle

Assets at the time of Invoice creation in Oracle Payables.
Oracle General Ledger: Once we move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.
Oracle Payables: After this the supplier sends the invoice for the purchased goods and we Enter or Match the invoice against the PO from Oracle Purchasing in Oracle

Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.
Oracle General Ledger: When we enter the invoice it means that we have created a Liability against that supplier and also we have recorded the expense incurred or asset

purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.
Oracle Payables: pay the invoice and settle the Liability.
Oracle General Ledger: The liability is settled and our cash movement account is updated.
Oracle Cash Management: As we pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled,

Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.
Oracle General Ledger: our cash at bank is updated with actual balance.
Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM): we start the manufacturing of our final product. Both OPM or ODM requests the different raw

materials from our inventory organizations and manufactures a finished good.
Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count

from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.
Now the final product is ready to be sold in the market and from here the O2C cycle starts.

Order to Cash Cycle:
Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with

the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Oracle Order Management: Customer places the order.
Oracle Order Management: You enter the customer order
Oracle Inventory: Check the available unit and the quantity ordered by the customer.
Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.
Oracle Receivables: The customer receives the product and you invoice the customer.
Oracle General Ledger: You record your revenue and receivables.
Oracle Receivables: The customer pays and you receive the cash/check.
Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank

balance or Oracle General Ledger.
Oracle General Ledger: You have the actual bank balance.
This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them.

Post from Know oracle

Oracle Applications - A Simple Way

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.

The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY. Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.
Oracle Accounts Payables handles all the payments to the vendors.
Oracle Inventory deals with the items you maintain in stock, warehouse etc.
Order Management helps you collect all the information that your customers order.
Oracle Receivables help you collect the money for the orders that are delivered to the customers.
Oracle Human Resources helps maintain the Employee information, helps run paychecks etc.
Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.
Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House

Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number
DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns


Oracle Reports
Oracle Form
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

KnowOracle

Differentiates between a Party and Customer

In AR (Account receivables ) or TCA ( Trading community architecture ) , we usually comes across two common terms, party and customer. though both link each other still there are always confusion, Below is the difference between party and customer , hope this will be helpful

Points differentiates a Party and Customer which is as follows

Party

a) Prospective Customer and more relevant for CRM Purposes
b) No Business Transactions involved (Sales Order, Sales Invoice, Debit Memo, Credit Memo, Receipt etc.,)
c) A Party does not have account but have Sites
d) A Party can exist without Customer Record
e) A Party Record will not have record in following tables

HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCT_RELATE_ALL

Customer

a) A Customer which is used both in CRM as well as in OM,Financials or any other module.Example (A Sales Order in OM or Invoice in Receivables cannot be created without creating a Customer record for the Party).
b) A Business Transaction like a Sales Order, Invoice,Debit Memo,Credit Memo,Receipt can be created.
c) A Customer will have account and as well as Sites.
d) A Party record is must to create a Customer Record linked through party_id.
e) A Customer Record will have records in following tables

HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCT_RELATE_ALL

with reference to party_id column.

Run the Party and Customer Diagnostic Report to know more about the table information.

Important Note:

For Example Party ‘A’ has ‘B’ and ‘C’ two Customer accounts and party ‘X’ has ‘Y’ and ‘Z’ two customer accounts. If you want to merge Customer Accounts ‘B’ and ‘C’ with ‘Y’ and ‘Z’, then first we need to perform Party merge and then perform the customer merge. It operates on the simple logic, First Parent records need to be merged before merging the child records

KnowOracle

SQL Script to list object privileges granted to a particular user

Login to database as sys / system / or any user who had dba privileges and run below command

select grantor 'granted' privilege 'on' table_name 'owned by' owner 'to' grantee from dba_tab_privs where grantee like upper('&GRANTEE_NAME') order by privilege;
where GRANTEE_NAME is the user you want to find out the privileges.

Even though the view name is dba_tab_privs, it lists privileges on all objects including tables, views, procedures, etc.,

Sample Output

APPS granted DELETE on FND_USER_RESP_GROUPS owned by APPS to XXXXXX
APPLSYS granted DELETE on FND_USER owned by APPLSYS to XXXXXX;

OraclePitStop

AR Receipts API Single Insert Creation Script

1.Creating a RECEIPT

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_cr_id NUMBER;

BEGIN
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'USD',
p_amount => 200,
p_receipt_number => 'XX_TEST1',
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_customer_id => Customer id ,
p_customer_bank_account_id => ,
p_customer_site_use_id => ,
p_receipt_method_id => ,
p_cr_id => l_cr_id );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF
l_msg_count > 1
THEN LOOP
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

2. Applying to an Invoice

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);

BEGIN
ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cash_receipt_id => ,
p_customer_trx_id => ,
p_applied_payment_schedule_id => ,
p_amount_applied => ,
p_show_closed_invoices => 'Y',
p_apply_date => SYSDATE,
p_apply_gl_date => SYSDATE,
p_line_number => 1 );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF l_msg_count > 1
THEN LOOP
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;

3. UNApplying a receipt to an Invoice

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2 (240);
l_mesg VARCHAR2 (240);
p_count NUMBER;

BEGIN
arp_standard.enable_debug;
arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log');

ar_receipt_api_pub.unapply
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cash_receipt_id => &cash_receipt_id,
p_applied_payment_schedule_id => &applied_payment_schedule_id,
p_reversal_gl_date => '&reversal_gl_date' );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF l_msg_count > 1
THEN LOOP
p_count := p_count + 1;
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

4.CREATE AND APPLY A Receipt

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2 (240);
l_mesg VARCHAR2 (240);
p_count NUMBER;

BEGIN
DBMS_OUTPUT.put_line ('Start');
arp_standard.enable_debug;

ar_receipt_api_pub.create_and_apply
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_currency_code => 'USD',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_amount => ,
p_receipt_number => ' ',
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_customer_id => ,
p_customer_bank_account_id => ,
p_customer_site_use_id => ,
p_receipt_method_id => ,
p_trx_number => ' ',
p_cr_id => l_cash_receipt_id ); D

BMS_OUTPUT.put_line ('Return_status ' l_return_status);
DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF
l_msg_count > 1
THEN LOOP
p_count := p_count + 1;

l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

5. Miscellanous receipt Creation

DECLARE

l_return_status VARCHAR2 (1);

l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER; l_receipt_number VARCHAR (10);BEGIN l_receipt_number := '123'; DBMS_OUTPUT.put_line ('Start'); arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.create_misc (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_currency_code => 'USD', p_amount => &amount, p_receipt_date => '&receipt_date', p_gl_date => '&gl_date', p_receipt_method_id => &receipt_method_id, p_activity => 'Interest Income', p_misc_receipt_id => l_cash_receipt_id, p_receipt_number => l_receipt_number ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

6.REVERSING a Receipt

DECLARE

l_return_status VARCHAR2 (1);

l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER;BEGIN arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.REVERSE (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_receipt_number => '&receipt_number', p_reversal_category_name => 'Reverse Payment', --values can be modified. p_reversal_reason_name => 'Nsf' -- values can be modified. ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ( 'l_msg_data A ' RTRIM (LTRIM (l_msg_data)) '****' ); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

SQL Query to list running concurrent requests

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC

How to compile a single jsp file in Oracle Applications 11i/R12

You can follow below steps to compile a single jsp file (seeded or custom) in Oracle Applications 11i/R12.

1. Copy the jsp file to $COMMON_TOP/webapps/oacore/html (this location translates to $OA_HTML).

Placing the file under $OA_HTML enables end users to access the file using the url http://hostname.domain:port/OA_HTML/[jspfile.jsp]
2. export PATH=$PATH:$FND_TOP/patch/115/bin
3. ojspCompile.pl - -compile -s ‘[jspfile.jsp]‘ (no space between the two dashes before compile)

No need to bounce apache server after compiling the file to access the new content.

Below is the command line help for ojspCompile.pl command

ojspCompile.jsp command help

syntax: ./ojspCompile.pl COMMAND {ARGS}
COMMAND –compile update dependency, compile delta
–create rebuild entire dependency file
-delta.out update dependency, list delta to file
-dep.out update dependency, output heirarchy to file

ARGS -s matching condition for JSPs filenames
-p number of parallel compilations
-log to override logfile from ojspCompile.conf You are
recommended to set the log file location
outside of any network file system shared (NFS) area/drive.
-conf to override ojspCompile.conf
–retry retry previously failed compilation attempts
–flush forces recompilation of all parent JSPs
–quiet do not provide an actively running progress meter
–fast instantly fail jsps that are *possibly* invalid

example1: ojspCompile.pl –compile -s ‘jtf%’ -p 20 –retry
example2: ojspCompile.pl –compile -s ‘jtflogin.jsp,jtfavald.jsp’ –flush
example3: ojspCompile.pl –compile –fast –quiet

Post from OraclePitStop

SQLLOAD with multi-byte characters when data is in Excel

To SQLLOAD with multi-byte characters when data is in Excel

1.Add a header line at the top of the page (if one doesn’t exist)
2.Save as a Unicode text file
3.Open the saved Unicode file in Word
4.Remove any tabs. Edit->Replace (you may have to cut and paste a tab in from notepad to do
this) – Replace All
5.The delimiter used above may be in double quotes - remove the quotes. For instance,
Edit->Replace “,” , replace all
6.Save the file as plain text, then Unicode (UTF-8). Note: Saving as just Unicode will not work
7.Binary ftp the file to Unix only using command line
8.Make sure to specify Options (skip =1) in your ctl file to skip the header

Note: the reason for the ensuring there is a header is that some characters are inserted at the beginning of the first line. If we make it the header and then skip it anyway, we don’t need to worry about deleting those characters.

Sample datafile:

Site, Rep
1000,仕切値について,
1001,仕切値について,
1002,仕切値について,
1003,仕切値について,

Sample CTL file (characterset should be optional):

-------------------------------------------------------------------------
--
-- Description: SQL*Loader control file to load data from level 1
--
-- Date:
-------------------------------------------------------------------------
OPTIONS (skip = 1)
Load Data
--Infile 'load2.txt'
Characterset UTF8
REPLACE
INTO TABLE sims.LEVEL1_SALESREP_UPD
Fields terminated BY ','
optionally enclosed BY '"'
trailing nullcols
(
site_number,
lv1_rep
)

Sample load command: sqlldr userid=apps/ control=load.ctl data=load3.txt

General Sql Queries

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL )
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects WHERE
ROWNUM<= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)

Counts

Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count.

SELECT catalog_nbr, COUNT (*) as Num_of_Stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY catalog_nbr

Sums

Provides a sum of all (distinct) values in a particular column. The column must be numeric. Null values in the column are not included in the sum

SELECT acad_career, subject, SUM (unt_taken) units_taken
FROM XX_STUDENT
WHERE subject = 'SOIL'
GROUP BY acad_career, subject

Having

Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query

SELECT catalog_nbr, class_section, COUNT (*) AS num_of_stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
AND component_main = 'DIS'
GROUP BY catalog_nbr, class_section
HAVING COUNT (*) > 50

Case/Decode/NVL

Case and Decode statements both perform procedural logic inside a SQL statement without having to use PL/SQL.

SELECT DISTINCT a.NAME,
CASE
WHEN a.emailid_2 IS NULL THEN a.emailid_1
ELSE a.emailid_2
END
email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
DECODE (a.emailid_2, NULL, a.emailid_1, a.emailid_2) email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
NVL (a.emailid_2, a.emailid_1) email_add
FROM XX_STUDENT a, XX_STUDENT1 b WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

All of these queries will return a list of student names with there secondary email addresses unless they didn’t report a secondary address, then it will return their primary email address

It is best to use the CASE statement when comparing ranges or more complex logic.

SELECT a.NAME,
(CASE
WHEN a.vac_hrs_taken_ytd <= 40 THEN 'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN 41 AND 100 THEN 'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >= 101 THEN 'WELL RESTED'
END) mental_wellbeing
FROM XX_STUDENT a
WHERE a.deptid = '831A'
AND a.fisc_yr = '2003'
AND a.pay_period = '06'
AND a.empl_status = 'A'
ORDER BY 2

Rollup

The use of a ROLLUP clause in the GROUP BY part of the SQL expression displays subtotals and grand totals depending on it’s use.

SELECT NVL (catalog_nbr, 'GRAND_TOTAL') catalog_nbr, class_section,
SUM (unt_taken)
total_units, COUNT (*) num_of_stu
FROM XX_STUDENT WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY ROLLUP (catalog_nbr, class_section)

Inline Views

You can use a SQL statement in the FROM clause of a SQL statement. This is called a inline view. Oracle treats the data set that is returned from the inline view as if it were a table.

SELECT a.NAME, a.office1_phone
FROM XX_STUDENT a,
(SELECT x.emplid
FROM XX_STUDENT1 x
WHERE x.deptid = '831A'
AND x.status_flg = 'C'
AND x.job_terminated ='N') b
WHERE a.emplid = b.emplid;

Round

Returns a number rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer

SELECT ROUND(AVG(ENG_ACT_SCORE), 4)
FROM XX_STUDENT
WHERE ENG_ACT_SCORE != 0

syntax how to set comments on a table or a column

syntax how to set comments on a table or a column

If we want to give a comment on a table :

COMMENT ON TABLE IS 'the comments should go here';

If we want to give a comment on a column : COMMENT ON TABLE COLUMN table.columnIS 'the comments should go here';

The entries of these comments on the table goes in the following tables:
For tables:
select * from all_tab_comments where table_name ='table_name'
(or)
select * from user_tab_comments where table_name = 'table_name'

For Columns

select * from all_col_commentswhere table_name = 'table_name'
(or)
select * from user_col_commentswhere table_name = 'table_name'

Few Database tables useful for Apps Developer

Few Database tables useful for Apps Developer

ad_bugs
ad_applied_patches
select * from V$SESSION where TERMINAL like 'XXXX'
select * from V$ACCESS where OBJECT like 'XX_%'
ALTER SYSTEM KILL SESSION 'Session_id,Serial'
select * from all_indexes

List all indexed columns for a given table

break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_namefrom user_indexes i, user_ind_columns cwhere i.index_name = c.index_name and i.table_name = upper('&&1')order by c.index_name, c.column_position
/

Print Oracle Apps versions

SELECT substr(a.application_short_name, 1, 5) code,substr(t.application_name, 1, 50) application_name, p.product_version versionFROM fnd_application a, fnd_application_tl t, fnd_product_installations pWHERE a.application_id = p.application_idAND a.application_id = t.application_idAND t.language = USERENV('LANG')

Delete concurrent program from backend

begin
fnd_program.delete_program('short name of the concurrent program to be deleted','Schema');
fnd_program.delete_executable('short name of the concurrent program to be deleted','Schema');
commit;
end;

Concurrent program Queries

Responsibilities to which assigned:

SELECT DISTINCT * FROM fnd_responsibility_tl
WHERE responsibility_id IN ( SELECT responsibility_id FROM fnd_responsibility_vl WHERE request_group_id IN ( SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id = (SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name = 'XXXX')) AND end_date IS NULL) AND "LANGUAGE" LIKE 'US' ORDER BY responsibility_name

Application name:

SELECT * FROM fnd_application "application name"
WHERE application_id IN (SELECT application_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX'))

concurrent program short name:

SELECT * FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX')

Execution file name:

SELECT * FROM fnd_executables
WHERE executable_id=(SELECT executable_id FROM fnd_concurrent_programs WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX'))

Requests groups:

SELECT * FROM fnd_request_groups "requests groups"
WHERE request_group_id IN (SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXXX'))

Appliocation Menus , Submenus assigned to User

select r.responsibility_name,f.user_menu_name, me.prompt "Main_Prompt",me.description "Main_Desc", metl.prompt "sub_prompt", metl.description "sub_desc" from apps.fnd_user fu, apps.fnd_user_resp_groups urg, apps.fnd_menus_vl f, apps.fnd_responsibility fr, apps.fnd_responsibility_tl r, apps.fnd_menus fm, apps.fnd_menu_entries fme, apps.fnd_menu_entries_tl me, apps.fnd_menus fm2, apps.fnd_menu_entries fme2, apps.fnd_menu_entries_tl metl where 1=1and fr.menu_id=f.menu_id AND fu.user_id = urg.user_id and (urg.responsibility_id = fr.responsibility_id and urg.responsibility_application_id = fr.application_id) and (fr.responsibility_id = r.responsibility_id and fr.application_id = r.application_id) and (fme.menu_id = me.menu_id and fme.entry_sequence = me.entry_sequence) and fr.menu_id = fm.menu_id and fm.menu_id = fme.menu_id and fme.sub_menu_id = fm2.menu_id and fm2.menu_id = fme2.menu_id and (fme2.menu_id = metl.menu_id and fme2.entry_sequence = metl.entry_sequence) and fr.RESPONSIBILITY_ID=XXXXand metl.language='US'and me.language='US'order by urg.description