To disable the Concurrent Program from Backend
whenever sqlerror continue
SET ECHO OFF
SET SERVEROUTPUT ON
SET LINESIZE 200
BEGIN
fnd_program.enable_program('XXCFIRPORINV', --CP Short Name
'Cisco Financials', -- Application Name
'N'); --Enable Flag 'Y' or 'N'
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Main Exception : '||sqlerrm);
END;
/
Oracle Applications – Find responsibilities associated with the users – using sql
Following sql will help you find the responsibilities associated with the users in oracle applications.
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = ‘&&username’
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = ‘&&username’
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1
How To Add Responsibility to USER using pl/sql? – Oracle Applications
Using the pl/sql you can add responsibility to any USER without having Application System Administrator Rights.
First, get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name
First, get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name
SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME
To add Responsibility of “Receivables Manager” to User Jiltin
BEGIN
fnd_user_pkg.addresp (‘JILTIN’,‘AR’,‘RECEIVABLES_MANAGER’,‘STANDARD’,‘Add Responsibility to USER using pl/sql’,SYSDATE,SYSDATE + 100);
COMMIT;
DBMS_OUTPUT.put_line (‘Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘ Responsibility is not added due to ‘” SQLCODE” SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
fnd_user_pkg.addresp (‘JILTIN’,‘AR’,‘RECEIVABLES_MANAGER’,‘STANDARD’,‘Add Responsibility to USER using pl/sql’,SYSDATE,SYSDATE + 100);
COMMIT;
DBMS_OUTPUT.put_line (‘Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘ Responsibility is not added due to ‘” SQLCODE” SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
Oracle Group By ROLLUP
It would be safe to assume that most if not all of us are familiar with Oracle "Group By" clause. You can however use the ROLLUP function to enhance the grouping actions performed within your queries.
Lets take a look at a fairly complex Group By example and then re-write it using the Group By ROLLUP.
For example, if you want to print the monthly total sales for each region, you would probably execute the following query:
As expected, this report prints the total sales for each region and month combination. However, in a more complex application, you may also want to have the subtotal for each region over all months, along with the total for all regions, or you may want the subtotal for each month over all regions, along with the total for all months. In short, you may need to generate subtotals and totals at more than one level. Lets see how you go about doing this without using ROLLUP.
The 1st 36 rows list the sales for each month for every region. The last 3 rows however list the total per region as well as the sum for all sales. Lets see how we can accomplish the same results using Group By ROLLUP:
Same results we got using only Group By but with a lot less code and of course a lot lower SQL cost.
If you want to generate subtotals for each month instead of for each region, all you need to do is change the order of columns in the ROLLUP operation.
Lets take a look at a fairly complex Group By example and then re-write it using the Group By ROLLUP.
For example, if you want to print the monthly total sales for each region, you would probably execute the following query:
SELECT reg.region,
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;
REGION MONTH SUM(ORD.TOTAL_SALES)
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
As expected, this report prints the total sales for each region and month combination. However, in a more complex application, you may also want to have the subtotal for each region over all months, along with the total for all regions, or you may want the subtotal for each month over all regions, along with the total for all months. In short, you may need to generate subtotals and totals at more than one level. Lets see how you go about doing this without using ROLLUP.
SELECT reg.region,
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;
UNION ALL
SELECT reg.region, NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region
UNION ALL
SELECT NULL, NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id;
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;
UNION ALL
SELECT reg.region, NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region
UNION ALL
SELECT NULL, NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id;
REGION MONTH SUM(ORD.TOT_SALES)
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Southeast US January 1137063
Southeast US February 1855269
Southeast US March 1967979
Southeast US April 1830051
Southeast US May 1983282
Southeast US June 1705716
Southeast US July 1670976
Southeast US August 1436295
Southeast US September 1905633
Southeast US October 1610523
Southeast US November 1661598
Southeast US December 1841100
Mid-Atlantic 18923298
New England 19756923
Southeast US 20605485
59285706
40 rows selected.
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Southeast US January 1137063
Southeast US February 1855269
Southeast US March 1967979
Southeast US April 1830051
Southeast US May 1983282
Southeast US June 1705716
Southeast US July 1670976
Southeast US August 1436295
Southeast US September 1905633
Southeast US October 1610523
Southeast US November 1661598
Southeast US December 1841100
Mid-Atlantic 18923298
New England 19756923
Southeast US 20605485
59285706
40 rows selected.
The 1st 36 rows list the sales for each month for every region. The last 3 rows however list the total per region as well as the sum for all sales. Lets see how we can accomplish the same results using Group By ROLLUP:
Syntax:
SELECT . . .
FROM . . .
GROUP BY ROLLUP (ordered list of grouping COLUMNS);
SELECT . . .
FROM . . .
GROUP BY ROLLUP (ordered list of grouping COLUMNS);
SELECT reg.region,
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY ROLLUP (reg.region, ord.month);
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY ROLLUP (reg.region, ord.month);
REGION MONTH SUM(ORD.TOT_SALES)
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
New England 19756923
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Mid-Atlantic 18923298
Southeast US January 1137063
Southeast US February 1855269
Southeast US March 1967979
Southeast US April 1830051
Southeast US May 1983282
Southeast US June 1705716
Southeast US July 1670976
Southeast US August 1436295
Southeast US September 1905633
Southeast US October 1610523
Southeast US November 1661598
Southeast US December 1841100
Southeast US 20605485
59285706
40 rows selected.
-------------------- --------- ----------------
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
New England 19756923
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Mid-Atlantic 18923298
Southeast US January 1137063
Southeast US February 1855269
Southeast US March 1967979
Southeast US April 1830051
Southeast US May 1983282
Southeast US June 1705716
Southeast US July 1670976
Southeast US August 1436295
Southeast US September 1905633
Southeast US October 1610523
Southeast US November 1661598
Southeast US December 1841100
Southeast US 20605485
59285706
40 rows selected.
If you want to generate subtotals for each month instead of for each region, all you need to do is change the order of columns in the ROLLUP operation.
GROUP BY ROLLUP (ord.month, reg.region); instead of GROUP BY ROLLUP (reg.region, ord.month);
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
- Putt
Oracle AR -Difference between PARTY and CUSTOMER
Difference between PARTY and CUSTOMER?
Marketing is the process of identifying, creating and retaining a customer. CRM tools essentially deal with that process. It is used by companies to identify potential customers, make them buy something from the company, maintain effective relationships with them to retain them for repeat purchases as well. This is what CRM does.
But an ERP like oracle does not have any relationship with a person unless he buys something from the company. A customer is someone who has a buying relationship with the company. So how do we distinguish between a person who has bought something and someone who hasnt purchased anything from you? This is where the party definition comes into picture. A party is anybody that exists. He/She/It does not have to have bought something from the company earlier. In fact, they do not have any relationship whatsoever with the company. So how does the company make them buy something? This is where CRM helps. It helps the company in identifying such people, create some marketing campaigns to make them buy something and convert them into the company’s customers, and keep maintaining relationships with them so that they will keep buying from them in the future as well.
Oracle purchased Siebel CRM to target these companies which require CRM tools to create, maintain and retain new customers. Prior to 11i, Oracle had the customer tables only. It did not have to deal with non-customers. Anyone who purchases becomes a customer and is stored in the ar customer tables. When Oracle bought CRM, it needed to incorporate the concept of a ‘potential” and “repeat” customer into the existing data model. It could not have had separate schemas or databases for CRM tools and ERP tools. Since CRM and ERP have to talk to each other, they wanted one single model to incorporate customers who bought and people who have not purchased. So they created a layer over the customer definition called party and said “Anyone who exists is a party”. Once this party buys something, it becomes a customer. Kindly note that it is still a party. It is both a party and customer as well after the purchase is made. So for a person to become a customer, he has to exist first. Only then he can make the purchase and become a customer.
So the process would be like this. A company wants to create some new customers. So it first uses some CRM tools to identify new people it thinks can be targeted. It uses some CRM modules like marketing, telesales, telemarketing etc… to target the customer and finally convinces the customer to make the purchase. So, this person has entered the oracle database. Remember he has not bought anything yet. He is only a party for now. How does he buy now? CRM does not have any modules to place or process orders and payments. The party has to place an order in the ERP module Order management. Once he places an order, he becomes a customer too. So he is now both a party and customer. I hope this helps in understanding why some companies need CRM and ERP, and why Oracle bought siebel CRM. Oracle can target companies which deal with a lot of customers in its business like retailers (Walmart, Amazon, Target) and can sell them both CRM and ERP packages as part of a wholesome solution. since ERP and CRM are sold together, they need to communicate effectively and easily with each other and hence one single data model for customers called the “Party model”.
If you understood the above concept, you can easily answer the below questions.
1.When we create New Customer in ERP, why a PARTY will be created automatically?
A: Can a person make a purchase without existing? He has to exist first to make a purchase. He has to become a party first before becoming a customer. Once you place order in OM, the person is created as a party and customer as well. The party information can be used by CRM tools to target the person for future purchases.
2. Can I create a Customer without Party?
A: Can a person make a purchase without existing?
3.Can I create a Party without Customer?
A: Can a person exist independently without making a purchase? He can. So a party can exist without making a purchase. Imagine you went to a store, checked some stuff and did not buy anything. You might be asked to fill some information like your name, your address, what you liked,what you didnt like and things like that. So this information has to be stored somewhere. Here you will be entered in the company’s system as a party, but not a customer. they will then start sending you emails or offers trying to make you buy something using their CRM tools. once you buy something, you become a customer. Until you buy, you are only a party.
1. When we create New Customer in ERP, why a PARTY will be created automatically?
2. Can I create a Customer without Party?
3. Can I create a Party without Customer?
There is already a lot of material out there on this topic. There are a couple of articles on this, on oracle forums as well. I will try to explain this as elaborately as possible and not waste my “thanksgiving holiday”. I can get really pedantic at times, when im bored. Oracle as a company was basically into ERP solutions only. As part of its efforts and vision to branch out into every other application such as SCM, CRM and other domains, it started acquiring smaller companies. It acquired Siebel which was one of the industry leaders in the CRM domain. Just to provide some insight into CRM, it is an acronym for Customer Relationship Management. Any CRM is basically a marketing tool. If you understand the definition of the word “marketing”, you will easily understand the difference between a party and customer.Marketing is the process of identifying, creating and retaining a customer. CRM tools essentially deal with that process. It is used by companies to identify potential customers, make them buy something from the company, maintain effective relationships with them to retain them for repeat purchases as well. This is what CRM does.
But an ERP like oracle does not have any relationship with a person unless he buys something from the company. A customer is someone who has a buying relationship with the company. So how do we distinguish between a person who has bought something and someone who hasnt purchased anything from you? This is where the party definition comes into picture. A party is anybody that exists. He/She/It does not have to have bought something from the company earlier. In fact, they do not have any relationship whatsoever with the company. So how does the company make them buy something? This is where CRM helps. It helps the company in identifying such people, create some marketing campaigns to make them buy something and convert them into the company’s customers, and keep maintaining relationships with them so that they will keep buying from them in the future as well.
Oracle purchased Siebel CRM to target these companies which require CRM tools to create, maintain and retain new customers. Prior to 11i, Oracle had the customer tables only. It did not have to deal with non-customers. Anyone who purchases becomes a customer and is stored in the ar customer tables. When Oracle bought CRM, it needed to incorporate the concept of a ‘potential” and “repeat” customer into the existing data model. It could not have had separate schemas or databases for CRM tools and ERP tools. Since CRM and ERP have to talk to each other, they wanted one single model to incorporate customers who bought and people who have not purchased. So they created a layer over the customer definition called party and said “Anyone who exists is a party”. Once this party buys something, it becomes a customer. Kindly note that it is still a party. It is both a party and customer as well after the purchase is made. So for a person to become a customer, he has to exist first. Only then he can make the purchase and become a customer.
So the process would be like this. A company wants to create some new customers. So it first uses some CRM tools to identify new people it thinks can be targeted. It uses some CRM modules like marketing, telesales, telemarketing etc… to target the customer and finally convinces the customer to make the purchase. So, this person has entered the oracle database. Remember he has not bought anything yet. He is only a party for now. How does he buy now? CRM does not have any modules to place or process orders and payments. The party has to place an order in the ERP module Order management. Once he places an order, he becomes a customer too. So he is now both a party and customer. I hope this helps in understanding why some companies need CRM and ERP, and why Oracle bought siebel CRM. Oracle can target companies which deal with a lot of customers in its business like retailers (Walmart, Amazon, Target) and can sell them both CRM and ERP packages as part of a wholesome solution. since ERP and CRM are sold together, they need to communicate effectively and easily with each other and hence one single data model for customers called the “Party model”.
If you understood the above concept, you can easily answer the below questions.
1.When we create New Customer in ERP, why a PARTY will be created automatically?
A: Can a person make a purchase without existing? He has to exist first to make a purchase. He has to become a party first before becoming a customer. Once you place order in OM, the person is created as a party and customer as well. The party information can be used by CRM tools to target the person for future purchases.
2. Can I create a Customer without Party?
A: Can a person make a purchase without existing?
3.Can I create a Party without Customer?
A: Can a person exist independently without making a purchase? He can. So a party can exist without making a purchase. Imagine you went to a store, checked some stuff and did not buy anything. You might be asked to fill some information like your name, your address, what you liked,what you didnt like and things like that. So this information has to be stored somewhere. Here you will be entered in the company’s system as a party, but not a customer. they will then start sending you emails or offers trying to make you buy something using their CRM tools. once you buy something, you become a customer. Until you buy, you are only a party.
Purchase Order Interface
Interface Tables Used
1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE
INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)
INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);
INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);
Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);
Calling PO Approval workflow to approve POs
SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;
v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);
1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE
INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)
INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);
INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);
Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);
Calling PO Approval workflow to approve POs
SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;
v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);
Subscribe to:
Posts (Atom)