Delete Duplicate Records in Oracle
There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:
CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);
SELECT * FROM dup_test;
Use subquery to delete duplicate rows:
Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:
You can also detect and delete duplicate rows using Oracle analytic functions:
DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:
This is the simplest method to remove duplicity.
CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:
This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:
DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.
Read more: http://prasanthapps.blogspot.com/search/label/Delete%20Duplicate%20Records%20in%20Oracle#ixzz1pqHceEgU
===============
Delete concurrent program from backend
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;
Read more: http://prasanthapps.blogspot.com/search/label/Delete%20concurrent%20program%20from%20backend#ixzz1pqHgjiNP
===========
Difference Between Lexical and Bind Variable
Difference Between Lexical and Bind Variable
Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &ATABLE
===
Difference between "Internal" and "External" Drop-Ship
Difference between "Internal" and "External" Drop-Ship
In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN).
You should take a note,Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed.
where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.
Here is Functional test cases for Drop shipment
1)Enter an order for drop ship item
Responsibility: Order Management
Orders, Returns -> Order Organizer -> New
Under Main tab:
Enter Customer,Order Type and Price List. Verify that any defaulting takes place per rules setup.
Under Line Items tab:
Enter item (must be Purchasable), qty, schedule ship date. Ensure Selling Price populates correctly.
Under the Shipping tab:
Enter Source Type = External
Enter Receiving Org
Save the order.
2)Book the order
Hit the Book Order button.
The order header status should show Booked.
The order line status should be Awaiting Receipt.
This can be verified by viewing the Status field on the sales order line, or by going to
Tools -> Workflow Status
Note: If the line status does not show Awaiting Receipt, try manually progressing the order via:
Actions -> Progress Order
3)Run Requisition Import
Orders, Returns -> Requisition Import
Enter Import Source = Order Entry
Submit the request and verify the sales order. Check for Sales Order is updated with the req number or not by opening the order and going to the Line Items tab. Select Actions -> Additional Line Information
Under the Drop Ship tab you will see the requisition information.
4)Create a purchase order from the requisition
within Responsibility: Purchasing
Go to Autocreate
In the Find Requisition Lines window, enter the requisition number, clear the buyer and ship to fields and click the Find button.In the Autocreate Documents window, select the requisition line and click the Automatic button. In the New Document window,select the supplier and click the Create button. Record the PO number.
5)Approve the PO
When you are able to find Purchase Order click the Approve button.Ensure the Submit for Approval box is checked and click OK.
6)Receive against the PO
Navigate to :Receiving -> Receipts
Once you select organization, find the details by passing PO number.Now Tab through the Receipt Header window to the Lines window.Complete the details.
7)Initiate Receiving Transaction
Receiving -> Receiving Transactions Summary, In the Find Expected receipts form, enter the PO number and hit Find button.
You need to check the box to the far left of the Receiving line and enter a Subinventory.Save and verify that the concurrent program Receiving Transaction Processor completes successfully.
Verify the transactions
Receiving -> Transactions -> Summary
Under Supplier and Internal tab, enter receipt number and hit Find button to get the details.
9)Now Verify Sales Order status updated
Responsibility: Order Management
Orders, Returns -> Order Organizer
Find details for order number and navigate to the Lines tab, find out the detail for status , it should be 'Shipped'.Shipped Qty should be updated to reflect the full quantity ordered.
(This test cases for drop shipment flow Adopted from Metalink on reader demand.)
Similar Post on Drop Shipment
Understand “Drop Shipment” in Order Management?
Understand “Drop Shipment” in Order Management? -Part II
‘Drop Shipment’, ‘BackOrders’ and ‘Back to Back Order’ …
Drop Shipment - Functional Setup and flow
"Internal" versus "External" Drop-Ship: What’s the difference?
=============
Difference between Party and Customer
Difference between Party and Customer
Buzz It
PARTY
CUSTOMER
Prospective Customer and more relevant for CRM Purposes
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).
No Business Transactions involved (Sales Order, Sales Invoice, Debit Memo, Credit Memo, Receipt etc.,)
A Business Transaction like a Sales Order, Invoice, Debit Memo, Credit Memo,Receipt can be created.
A Party does not have account but have Sites
A Customer will have account and as well as Sites.
A Party can exist without Customer Record
A Party record is must to create a Customer Record linked through party_id.
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
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.
===========
Duplicate Vendor List
Duplicate Vendor List
select pv1.vendor_name,
pv2.vendor_name DUP_VENDOR_NAME,
pv1.segment1 VENDOR_ID,
pv2.segment1 DUP_VENDOR_ID,
pvsa1.vendor_site_code SITE_CODE,
pvsa2.vendor_site_code SITE_CODE,
pvsa1.address_line1,
pvsa2.address_line1 DUP_ADDRESS_LINE1,
pvsa1.zip
from po_vendors pv1,
po_vendors pv2,
po_vendor_sites_all pvsa1,
po_vendor_sites_all pvsa2
where pvsa1.vendor_site_id <> pvsa2.vendor_site_id
and substr(replace(pvsa1.address_line1, ' '),1,20) =
substr(replace(pvsa2.address_line1, ' '),1,20)
and pvsa1.zip = pvsa2.zip
and pv1.vendor_id = pvsa1.vendor_id
and pv2.vendor_id = pvsa2.vendor_id
and pv1.vendor_id <> pv2.vendor_id
and pvsa1.address_line1 <> 'YOUR ADDEDSS’
order by 1;
================
Duplicate rows in the table
Duplicate rows in the table:-
--------------------------------
The following query can be used to get the duplicate records from table.
SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY'Your duplicate values field name');
Example:-
SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);
To eliminate/delete the duplicate rows from the table, you can use the following query.
DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');
Example:-
DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);
===========
Example of $FLEX$ Syntax
Example of $FLEX$ Syntax
Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:
Segment Name Manufacturer
Value Set Name Car_Maker_Name_Value_Set
Validation Table CAR_MAKERS
Value Column MANUFACTURER_NAME
Description Column MANUFACTURER_DESCRIPTION
Hidden ID Column MANUFACTURER_ID
SQL Where Clause (none)
Segment Name Model
Value Set Name Car_Model_Name_Value_Set
Validation Table CAR_MODELS
Value Column MODEL_NAME
Description Column MODEL_DESCRIPTION
Hidden ID Column MODEL_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set
Segment Name Color
Value Set Name Car_Color_Name_Value_Set
Validation Table CAR_COLORS
Value Column COLOR_NAME
Description Column COLOR_DESCRIPTION
Hidden ID Column COLOR_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set
AND MODEL_ID =
:$FLEX$.Car_Model_Name_Value_Set
In this above example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.
Read more: http://prasanthapps.blogspot.com/search/label/Example%20of%20%24FLEX%24%20Syntax#ixzz1pqI3VgkB
==========
FAQs in Account Payable
FAQs in Account Payable
How many types of Invoices we can create in Oracle Payables?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice
Describe the 4-Way Matching?
2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)
3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)
4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)
2-Way (Invoice to Purchase Order)
Quantity billed vs. quantity ordered on shipment line
Invoice unit price vs. purchase order line unit price
3-Way (Invoice to Purchase Order and Receipt)
2-Way match criteria AND
Quantity billed vs. quantity received
Item = Receipt Required
4-Way (Invoice to Purchase Order and Receipt and Inspection)
3-Way match criteria AND
Quantity billed vs. quantity accepted
Note: Quantity accepted = (Quantity received – quantity rejected)
Item = Inspection Required
How many Holds we have?
System Holds: Tax, Quantity Match, Po amount with Invoice Amount
Manual Holds: Invoice Limit, Hold on Invoice
Can you Release Manual Holds? If Yes, How?
Yes. Holds – Release Holds
How many ways you can pay the Invoice Amount?
Apply in Full
Schedule Payments
Installments
What is the difference between Debit and Credit Memo?
Debit Memo will raise the Customer
Credit Memo will raise the Vendor
Can you create a Duplicate Vendor ?
No
Vendor Number Should be Generate Automatic or Manually?
Both, Manual And Automatic
How many key flexfields are there in Payables?
No key flexfields in PO,AP
What are Payment Terms?
Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.
What are the Payment Methods available?
• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.
What are the line types of an Invoice?
Item: Item lines capture the details of the goods and services billed on your invoice.
Freight: Freight lines capture the details of your freight charges.
Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.
Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.
What are the Invoice Distribution Types?
Item
Freight
Miscellaneous
Tax
Withholding
Prepayment
What are Holds in AP?
Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.
What are the Interface Tables in AP?
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS
What is the API to cancel single AP Invoice?
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
What is the API to find invoice status?
AP_INVOICES_PKG.GET_APPROVAL_STATUS
Give some of the Oracle Payables interface?
a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching
What are the key tables where supplier bank information are stored?
IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL
What is meant by with-holding tax invoice?
A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.
What are all the Modules Interacting with AP?
Cash Management
Oracle iExpenses
General Ledger
Oracle Assets
Subledger Accounting (R12)
HRMS
Project Accounting
Purchasing/iprocurement
Global Accounting Engine (11i)
What are the mandatory setups in AP?
1- Financial Options
2- Define Suppliers
3- Define Payment Terms
4- Define Payment Methods
5- Define Banks and Banks Accounts And Banks Accounts Documents
6- Open AP Accounts Periods
What is pay date basis?
The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.
• Due
• Discount
What is the difference between quick payment and manual payment?
Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.
Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.
What is terms date basis?
Terms Date Basis is to calculate due date.
Due date is calculated 4way. Eg: payment term is 30days
Due date = Sysdate + 30days
Due date = Invoice date + 30days
Due date = Goods Receive Date + 30days
Due date = Invoice Received date + 30days
How you will transfer payables to general ledger?
When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.
What are Aging Periods?
Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.
What is a “Mixed” Invoice and how do I enter one?
Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.
For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.
To enter a Mixed invoice:
1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.
2. Match to purchase orders, and/or invoices
Read more: http://prasanthapps.blogspot.com/search/label/FAQs%20in%20Account%20Payable#ixzz1pqI7YTFq
===
FINANCIALS Interview Questions
FINANCIALS Interview Questions
Q: What are the 4 C's in Accounting?
A: In Oracle 11i 3c's i.e Currency Calendar Char of Account
In R12 4c's include sub-ledger account along the above
!
Q: What is delegation in iExpense?
A: You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate.
Q: What is the process/steps for Vendor Conversion?
A: Insert the Vendor info into the interface tables and perform the required validations:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
Run the below programs to load the data into the Base tables:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
Q: What is Debit Memo & Credit Memo in Payables?
A: Credit Memo is a negative amount invoice you receive from a supplier representing a credit. Debit Memo is a negative amount invoice you send to notify a supplier of a credit you recorded for goods or services purchased.
Q: Explain the set up used for Automatic or Manual Supplier Numbering.
A: In the Financials Options window, you can set the Supplier Number entry option to either Autimoatic or Manual • Automatic: The system automatically assigns a unique sequential number to each supplier when you enter a new supplier. • Manual: You enter the supplier number when you enter a supplier
Q: What is Contract PO?
A: Contract PO is created when you agree with your suppliers on specific terms and conditions without indicating the goods and services that you will be purchasing.
Q: What is a Payable Document?
A: A medium you use to instruct your bank to disburse funds from your bank account to the bank account or site location of a supplier.
Q: In which table we can find the vendor number?
A: PO_VENDORS
Q: Give the cycle from creating an invoice to transferring it to GL in AP.
A: 1)Create Invoice 2)Validate Invoice 3)Create Accounting entries using Payables Accounting Process 4)Submit the Payables Transfer to General Ledger program to send invoice and payment accounting entries to the General Ledger interface. 4)Journal Import (GL) 5)Journal Post (GL)
Q: What are the different types of Invoices in Payables?
A: Standard, Credit Memo, Debit Memo, Expense Report,PrePayment, Mixed, PO Default
Q: You have created a new SOB. How will you attach this SOB to AP?
A: Go to Payables Manager for the appropriate Operating Unit.
Navigation:Setup--->Set of Books--->choose.
Q: How many key flexfields are there in Payables?
A: No Key Flexfields in AP
Q: What is the Distribution Type while entering the Invoice?
A: Item, Tax, Miscellaneous,Freight, Withholding Tax
Q: What are the Prepayment types?
A: Temporary and Permanent
Q: What is Aging Periods?
A: Aging Periods window are the time periods for the Invoice Aging Report. The Invoice Aging Report provides information about invoice payments due during four periods you specify.
Q: Whats the difference between the "Payables Open Interface Import" Program and the "Payables Invoice Import" program?
A: Payables Open Interface -- for importing regular invoices Payables Invoice Import -- for importing expense reports. In 11i renamed as Expense Report Import.
Q: What is prepayment & steps to apply it to an Invoice?
A: Prepayment is a type pf invoice that you enter to make an advance
payment to a supplier or employee.
To Apply it to an Invoice ,in the Invoices window, query either the prepayment or the invoice to which you want to apply it. Choose the Actions button and select the Apply/Unapply Prepayment check box. Click OK.
Q: Can you hold the partial payment if yes then how?
A: Yes.
1.Go to the Invoice window. Go to the scheduled payments tab.
2.Click "Split" to split the scheduled payment into as many
payments as you wish.
3.Check "Hold" against the Payment line you wish to hold.
Q: How you will transfer payables to general ledger?
A: Create Accounting. Transfer the transactions to GL_Interface Import the Journals Post the Journals
Q: What program is used to transfer AP transactions to GL?
A: Payables Transfer to General Ledger Program
Q: What is use of AP Accounting Periods?
A: In Payables accounting periods have to be defined to enter and account for transactions in these open periods. Payables does not allow transaction processing in a period that has never been opened. These periods are restricted to Payables only. The period statuses available in Payables are Never Opened, Future,Open, Closed, and Permanently Closed.
Q: What are the different interface programs in AP?
A: Payables Open Interface Import to load Invoices and other transactions.
Supplier Open Interface Import to load Suppliers.
Supplier Sites Open Interface Import to load Supplier sites.
Supplier Site Contacts Open Interface Import to load Supplier Site contacts.
Q: What is Invoice Tolerance?
A: We can define the matching and tax tolerances i.e how much to allow for variances between invoice, purchase order, receipt, and tax information during matching. You can define both percentage–based and amount–based tolerances.
Q: What will accrue in Payables?
A: Expenses and Liabilities
Q: What is a Hold? Explain the types of Hold.
A: Payables lets you apply holds manually on an invoice, Payments etc to prevent the payment from being made or to prevent the accounting entries to be created etc. Some of the Payable holds are -- Invoice Hold, Accounts Hold, Funds Hold, Matching Hold, Variance Hold, Misc hold.
Q: Which module is the owner of Vendor/Supplier tables?
A: Purchasing
Q: What is Payment Terms?
A: Payment Terms let you define the due date or the discount date , due amount or discount amount. Once the payment terms are defined, you can attach these to the suppliers and supplier sites and these terms will be automatically populated once the invoice is entered for a supplier site.
Q: can we make a half payment in payables?
then how?
A: No answer yet!
Q: can we create invoice without PO in payables? then how?
A: No answer yet!
Q: In AP the suppliers didn’t visible in India Creditors Ledger Report Parameter?
A: pls check whether that particular supplier is available in Suppliers addition inforamtion or not.
Q: What kind of transactions can be created using AutoInvoice?
A: Invoices, credit memos, debit memos, and on–account credits can be imported using AutoInvoice.
Q: What are the underlying tables and validations required during AutoInvoice Interface?
A: Interface tables: RA_INTERFACE_LINES_ALL Base tables: RA_CUSTOMER_TRX_ALL RA_BATCHES RA_CUSTOMER_TRX_LINES_ALL AR_PAYMENT_SCHEDULES_ALL RA_CUSTOMER_TRX_LINE_SALESREPS RA_CUST_TRX_GL_DIST_ALL AR_RECEIVABLES_APPLICATIONS AR_ADJUSTMENTS RA_CUSTOMER_TRX_TYPES_ALL Concurrent Program: Auto invoice master program Validations: check for amount, batch source name, conversion rate, conversion type. Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity. Validate if the amount includes tax flag.
Q: Explain the different steps in implementing Autolockbox.
A: Import, Validate, Post Quick Cash
Q: What are the different Invoice matching types?
A: 2-way matching: 2-way matching verifies that Purchase order and invoice quantities must match within your tolerances 3-way matching: 3-way matching verifies that the receipt and invoice information match with the quantity tolerances 4-way matching: 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances
Q: What are the different Transaction types in AR?
A: Invoice, Credit Memo, Debit Memo,Charge back, commitments
Q: Tell me about TCA?
A: TCA canbe used to import or modify Customers related data.
Q: Name some Flexfields in AR.
A: Sales Tax Location, Territory
Q: Explain the steps involved in Transfer to GL from AR.
A: Transfer the transactions to GL_Interface Import the Journals Post the Journals
Q: What is the dbnumber of a particular cusotmer TCA?
A: It is a unique number used to identify the Customers.
Q: Where can you find the Customer payment terms?
A: In the table hz_customer_profiles
Q: What is the link between OM and AR?
A: To relate the Order Number (ONT) to the Invoice (AR) we use the LINE TRANSACTION FLEX FIELDS. In RA_CUSTOMER_TRX_ALL, INTERFACE_HEADER_ATTRIBUTE1 to INTERFACE_HEADER_ATTRIBUTE15 store this Information that uniquely identifies the Sales Order (HEADER INFO). In RA_CUSTOMER_TRX_LINES_ALL, INTERFACE_LINE_ATTRIBUTE1 to INTERFACE_LINE_ATTRIBUTE15 store this Information that uniquely identifies the Sales Order.(LINE INFO)
Q: how can we adjust the money in AR?
A: No answer yet!
Q: What is Blanket PO?
A: A Blanket PO is created 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 know the detail of your delivery schedules.
Q: What are different types of PO’s ?
A: Standard, Blanket, Contract, Planned
Q: How does workflow determines whom to send the PO for approval?
A: It Depends on the setup whether it is position based or supervisor based.
Q: Can you create PO in iProcurement ?
A: No
Q: Give me some PO tables
A: PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL,PO_DISTRIBUTIONS_ALL
Q: Tell me about PO cycle?
A: Create PO
Submit it for Approval
Receive Goods against the PO (when order is delivered by the vendor)
Close the PO after the invoice is created in AP and teh payments have been made to the vendor.
Q: Explain Approval Heirarchies in PO.
A: Approval hierarchies let you automatically route documents for approval. There are two kinds of approval hierarchies in Purchasing: position hierarchy and employee/supervisor relationships.
If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.
If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.
Q: What functions you do from iProcurement?
A: Create Requisition, Receive the goods.
Q: What is difference between positional hierarchy and supervisor hierarchy?
A: If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.
If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.
Q: What is the difference between purchasing module and iProcurement module?
A: Iprocurement is a self service application with a web shopping interface. WE can only create and manage requisitions and receipts.
Purchasing module is form based and also lets you create PO and many other functions are possible other than requisitions and receiving.
Q: What is dollar limit and how can you change it?
A: An approval group is defined which is nothing but a set of authorization rules comprised of include/exclude and amount limit criteria for the following Object Types: Document Total, Account Range, Item Range, Item Category Range, and Location that are required to approve a PO.
You can always change the rules by doing the below:
Navigation:
Purchasing Responsibility > Setup > Approvals > Approval Groups
Query the Approval group and change teh rules accordingly.
Q: What is Planned PO?
A: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
Q: What is Purchase order workflow ?
A: No answer yet!
Q: what are the tables link between PO & Gl technically?
A: No answer yet!
Q: What is backdated Receipt?
A: Creating a Receipt with "Receipt Date" less than sysdate or today's date is referred to as 'Backdated Receipt".
Q: Is it possible to create a backdated receipt with a receipt date falling in closed GL period?
A: No. The receipt date has to be with in open GL period.
Q: What is Receipt Date Tolerance?
A: The buffer time during which receipts can be created with out warning/error prior or later to receipt due date.
Q: How do we set the Receipt Tolerance?
A: Receipt Tolerance can be set in three different places.
1. Master Item Form (Item Level)
2. setup, organization form (Organization Level)
3. Purchase Order, Receiving controls. (shipment level).
Read more: http://prasanthapps.blogspot.com/search/label/FINANCIALS%20Interview%20Questions#ixzz1pqICa2Rs
================
FLEX FIELDS
FLEX FIELDS
Keyflexfields and the tables in which they store their code combinations.
AccountingFlexfield GL_CODE_COMBINATIONS
CategoryFlexfield FA_CATEGORIES_B
Asset KeyFlexfield FA_ASSET_KEYWORDS
LocationFlexfield FA_LOCATIONS
Oracle Service ItemFlexfield MTL_SYSTEM_ITEMS_B
TerritoryFlexfield RA_TERRITORIES
Sales Tax LocationFlexfield AR_LOCATION_COMBINATIONS
Item Categories MTL_CATEGORIES_B
Account Aliases MTL_GENERIC_DISPOSITIONS
Item Catalogs MTL_ITEM_CATALOG_GROUPS
Sales Orders MTL_SALES_ORDERS
System Items MTL_SYSTEM_ITEMS_B
Stock Locators MTL_ITEM_LOCATIONS
GradeFlexfield PER_GRADE_DEFINITIONS
JobFlexfield PER_JOB_DEFINITIONS
Personal AnalysisFlexfield PER_ANALYSIS_CRITERIA
PositionFlexfield PER_POSITION_DEFINITIONS
Soft CodedKeyFlexfield HR_SOFT_CODING_KEYFLEX
Bank DetailsKeyFlexField PAY_EXTERNAL_ACCOUNTS
Cost AllocationFlexfield PAY_COST_ALLOCATION_KEYFLEX
People GroupFlexfield PAY_PEOPLE_GROUPS
Descriptive Flexfield and the tables in which they store their code combinations.
Accounting Calendar: Calendar GL_PERIOD_SETS
Accounting Calendar: Periods GL_PERIODS
AutoAccounting Rules GL_IEA_AUTOGEN_MAP
AutoAllocationBatch GL_AUTO_ALLOC_BATCHES
AutoPostCriteria GL_AUTOMATIC_POSTING_OPTIONS
AutoReversalCriteria GL_AUTOREVERSE_OPTIONS
Automatic Posting Sets GL_AUTOMATIC_POSTING_SETS
Budget Types GL_BUDGET_TYPES
Budget Versions GL_BUDGET_VERSIONS
Budgetary Control Group: Group GL_BC_OPTIONS
Budgetary Control Group: Rules GL_BC_OPTION_DETAILS
Common Stocks: Share Activity GL_SHARES_ACTIVITY
Conversion Rate Types GL_DAILY_CONVERSION_TYPES
Value Set Usage by KeyFlexfields
select flex_value_set_name "Value Set",
descriptive_flexfield_name "Flexfield",
descriptive_flex_context_code "Structure",
end_user_column_name "Column"
from apps.fnd_descr_flex_column_usages a,
apps.fnd_flex_value_sets b
where a.flex_value_set_id = b.flex_value_set_id
and descriptive_flexfield_name not like '$SRS$%'
order by flex_value_set_name,
descriptive_flexfield_name,
descriptive_flex_context_code;
Value Set Usage by DescriptiveFlexfields
select d.flex_value_set_name "Value Set",
a.id_flex_name "Flexfield",
e.id_flex_structure_name "Structure",
c.segment_name "Segment"
from apps.fnd_id_flexsa,
apps.fnd_id_flex_structures b,
apps.fnd_id_flex_segments c,
apps.fnd_flex_value_sets d,
apps.fnd_id_flex_structures_tle
where a.application_id = b.application_id
and a.id_flex_code = b.id_flex_code
and b.application_id = c.application_id
and b.id_flex_code = c.id_flex_code
and b.id_flex_num = c.id_flex_num
and c.flex_value_set_id = d.flex_value_set_id
and c.application_id = e.application_id
and c.id_flex_code = e.id_flex_code
order by d.flex_value_set_name, a.id_flex_name,
e.id_flex_structure_name;
Read more: http://prasanthapps.blogspot.com/search/label/FLEX%20FIELDS#ixzz1pqIRIKgo
==========================
FAQ’s in SQL & PL/SQL
FAQ’s in SQL & PL/SQL
What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
What are the components of a PL/SQL Block?
Declarative part
Executable part
Exception part
What are the datatypes a available in PL/SQL?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are:
I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
What is difference between % ROWTYPE and TYPE RECORD?
% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.
Explain the two types of Cursors?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
What are the cursor attributes used in PL/SQL?
% ISOPEN – Used to check whether a cursor is open or not.
% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.
% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.
What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
What is the difference between implicit and explicit cursors?
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.
What are the different types of joins available in Oracle?
Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.
Self Join: If comparison comes in a single table
Cartesian Join: When tables are joined without giving any join condition.
Inner Join: The resultant set includes all the rows that satisfy the join condition.
Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition. The outer join operator Plus sign (+) will be included in the join condition.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
What is an autonomous transaction?
An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.
What is the difference between View and Materialized view?
Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.
What is dynamic SQL?
Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.
Can you use COMMIT in a trigger?
Yes but by defining an autonomous transaction.
What is the difference between anonymous blocks and stored procedures?
Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.
What is a package spec and package body? Why the separation?
Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.
Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.
What is Correlated Subquery?
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.
What is Sequence?
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
What is SQL Deadlock?
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
What is SQL*Loader?
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.
What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Explain forward declaration used in functions?
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.
Forward declarations are required in one specific situation: mutual recursion.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
What is the difference between Truncate and Delete Commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
What is the Purpose of HAVING Clause?
The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
What is INLINE View in SQL?
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
While creating a sequence, what does cache and nocache options mean?
With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
Does the view exist if the table is dropped from the database?
Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.
What is an Index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
What types of index data structures can you have?
An index helps to faster search values in tables. The three most commonly used index-types are:
B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
What is the difference between a “where” clause and a “having” clause?
“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.
Can a view be updated/inserted/deleted? If Yes – under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
What is tkprof and how is it used?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
What is explain plan and how is it used?
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
What are the Lock types?
Share Lock: It allows the other users for only reading not to insert or update or delete.
Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular object, others can only read.
Update Lock: Multiple user can read, update delete .
What is Pragma EXECPTION_INIT? Explain the usage?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
What is Raise_application_error?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.
What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.
What is the difference between Package, Procedure and Functions?
A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
Procedure is a sub program written to perform a set of actions and can return multiple values.
Function is a subprogram written to perform certain computations and return a single value.
Unlike subprograms packages cannot be called, passed parameters or nested.
How do you make a Function and Procedure as a Private?
Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.
What is Commit, Rollback and Save point?
Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.
Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
Rollback – This statement is used to undo work.
What is the difference between DDL, DML and DCL structures?
DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving. Ex; Commit, Rollback, Savepoint.
What is the difference between the snapshot and synonym?
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.
What is the difference between data types char and varchar?
Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement.
Tell some new features in PL/SQL in 10g?
-Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
-Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL
What is Overloading in PL/SQL?
Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.
What is a mutating and constraining table?
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
What is Nested Table?
A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows. In a sense, it’s a way of storing a one-to many relationship within one table.
What is Varying Array?
A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.
Give some most often used predefined exceptions?
a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)
Give some important Oracle supplied packages?
DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.
DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.
DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.
UTL_FILE: With this package, you can read from & write to Operating system files
UTL_HTTP: This package allows to make HTTP Requests directly from the database.
What is Instead Of Trigger?
This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.
What is the Sequence of Firing Database Triggers?
a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger
What is the Difference between PL/SQL Table & Nested Table?
PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.
What is the Difference between Nested Table & Varray?
Nested Tablea) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table
Varraya) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces
What are the various SQL Statements?
a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.
What is Rowid?
It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.
What is Partitioning?
It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning
What is a Cluster?
A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.
What is the Difference between Nested Subquery & Correlated Subquery?
Nested Subquerya) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query
Correlated Subquerya) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query
What is the Difference between Translate & Replace?
Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.
Read more: http://prasanthapps.blogspot.com/search/label/FAQ%E2%80%99s%20in%20SQL%20AND%20PL%2FSQL#ixzz1pqIXnGFr
================
GL Flow
GL Flow
GENERAL LEDGER
1. Creating Journal ( Batch, Header and Lines).
2. Creating Code Combination,
3. Creating Journal Source
4. Creating Journal Category
5. Understanding Accounting Period,
6. Understanding Key Flexfileds,
7. Understanding Descriptive Flexfields
8. Understanding SetUp of General Ledger
9. Understanding Account Inquiry form
10. Understanding GL Interface Program - Journal Import
11. Understanding Chart of Accounts.
12. Understanding Set of Books
Once you create a journal look for the data in below mentioned tables
Journal countains a Batch - GL_JE_BATCHES,
Header -GL_JE_HEADERS
Lines - GL_JE_LINES,
Once you create a Code Combination look for the data in GL_CODE_COMBINATIONS
Once you understand Account Inquiry Form look for the data in
GL_BALANCES
Once you create a Journal Source look for the data in
GL_JE_SOURCES
Once you create a Journal Category look for the data in GL_JE_CATEGORIES
Once you understand Accounting Period then check the data in
GL_PERIODS,
- GL_PERIOD_STATUSES,Once you understand Chart of Accounts structure then look for the data in,- GL_CHART_OF_ACCOUNTS
Once you understand Set of Books structure then look for the data in
- GL_SETS_OF_BOOKS
Once you understand GL Interface program then check tables
- GL_INTERFACE
- GL_IMPORT_REFERENCES
Go through the all the above tables again and again and be conversant about the joins.
Read more: http://prasanthapps.blogspot.com/search/label/GL%20Flow#ixzz1pqIbGgCO
===========
General Ledger Calendar FAQ [ID 134362.1]
General Ledger Calendar FAQ [ID 134362.1]
Applies to:
Oracle General Ledger - Version: 11.5 and later [Release: 11.5.0 and later ]
Oracle General Ledger - Version: 11.5 and later [Release: 11.5.0 and later]
Purpose
When investigating problems in this area the solution is often in the relevant White Paper which also provides a useful insight into the Topic. Please refer to the White Paper General Ledger Calendar Setup and Usage at Note:130539.1
Questions and Answers
1. You can change a period's specifications, except for the period type, as long as the period has not been used in a set of books.
You cannot change a calendar period that is open, closed, future enterable, or permanently closed in any set of books, or is included in an open budget or encumbrance year.
If it is difficult to use the periods as is, the best solution is to create a new set of books that uses a new calendar with the correct period dates. You can run Consolidation to move the General Ledger
balances from the old set of books to the new set of books. For step by step instructions on the Consolidation process, refer to the Oracle General Ledger User's Guides.
This may also mean that you need to re-install your subledgers so they will use the new set of books. To avoid this see Note:157642.1 How to Change the First Accounting Open Period
References: Note:76503.1 - Scripts to check calendar setup
2. Can I add an adjusting period to my calendar?
You can only define the number of periods that is specified by the Period Type you are using for your set of books. For example, if, for your set of books, you are using a Period Type of Month, and Month is defined to have 12 periods per year, you can only define 12 periods of Period Type Month for any one year, on the
Calendar form.
3. You defined a Period Type and now have decided that you want to change the periods per year that are associated with the Period Type. How can you do this?
Once you define a Period Type and save it, you cannot change it. If you need to change the number of periods then you have to define a new Period Type and a new Set of Books to use that new Period Type.
4. What are the year types 'Fiscal' and 'Calendar' used for ?
The Year Type (Fiscal or Calendar), is used only to determine which two digits to append to the system generated period name.
Regardless of which Year Type is used, the 'Year' entered on the Calendar form must be the same for all periods in your fiscal year - whether it is a calendar year or a fiscal year.
If Year Type = Calendar:
The last 2 digits of the 'From' date for the period are used.
If Year Type = Fiscal:
The last 2 digits of the value in the 'Year' are used.
References: Note.1013624.102 What are the year types 'Fiscal' and 'Calendar' used for?:
5. You are setting up a new set of books and calendar and not sure how much of the first year you need to define in the calendar. Do you need to define an entire year for the first year if you are only going to open the last period.
No, you do not have to define the entire year. You only define the periods you need, but of course without gaps between periods. Remember to define one prior period if you will be translating.
Example: You want to start entering transactions for January 2001, but you will be doing translations for January 2001 also, then you will need to define and open a period before that. In this example, December 2000. But it would be recommended to define all the periods so that the calender validation will not show any missing periods.
References: General Ledger User's Guide Note:1062487.6
6. Your calendar year is changing from fiscal year to calendar year and you already have a set of books that is using the fiscal year calendar. How can you change your calendar in GL?
There are 2 possible solutions. See Note 102460.1 for a full explanation.
Example:
- Your current fiscal year runs from April 1 through March 31.
- Your new year will run from January 1 through December 31.
- You will transition to the new year beginning on January 1 of the next year,
so you will have a short year from April 1 through December 31.
1) Create a new calendar and set of books
Create a new calendar, with the correct periods, and define a new set of books that uses that calendar. Run Consolidation to move your balances from the existing set of books to the new set of books.
You may also need to re-install your subledgers so they will use the new set of books.
OR
2) Update the existing calendar
a) Define the periods for your short year (April through December).
You must define the number of periods that are expected for the Period Type you are using. In this example, it is 12. So you will need to define 9 regular periods, and 3 adjusting periods. The Year
must be defined as the next sequential year.
b) Define the periods for the next year. This is the first year that runs from January through December. The Year must be defined as the next sequential year.
Note: This method depends on the subledgers taking their periods from General ledger so the periods must not already be defined. In the case of Fixed Assets, it would require major consultancy to change the year end period or YTD figures will always be inaccurate.
References: Note:102460.1 How to change GL Calendar from a Fiscal to a Calendar year?
7. You opened a period by mistake and want to set the status back to Future Enterable. How can you do this?
This is NOT supported. You can not set a period status back to future enterable after it has already been opened. If you do, it will cause corruption in the gl_balances table.
8. The year was specified incorrectly on your calendar periods. How can you fix this?
You cannot make changes to calendar periods in the form if the period is in an open budget year, open encumbrance year, or the status is Open, Future Enterable or Closed.
The solution is to create a new set of books that uses a new calendar with the correct period dates. You can run Consolidation to move the General Ledger data from the old set of books to the new set of books. For step by step instructions on the Consolidation process, refer to the Oracle General Ledger User's Guides.
This may also mean that you need to re-install your subledgers so they will use the new set of books.
9. The Year Type in your Period Types Form is not what you want. You used Fiscal instead of Calendar or visa versa.
The Year Type (Fiscal or Calendar), is used only to determine which two digits to append to the system generated period name.
References: Note:1013624.102 What are the year types 'Fiscal' and 'Calendar' used for?
10. Can you change a period name after the calendar is defined?
If the Calendar form lets you make the change, you can do it.
Otherwise, it means the period is in use, and you can not make the change.
11. What is the function of GL_DATE_PERIOD_MAP table and how is it populated?
The GL_DATE_PERIOD_MAP table stores the correlation between dates and non-adjusting periods for each accounting calendar and period type combination.
There is always a one-to-one correspondence between dates and non-adjusting periods since your accounting calendar must not have any gap or overlap between non-adjusting periods.
References
NOTE:1013624.102 - What are the year types 'Fiscal' and 'Calendar' used for?
NOTE:102460.1 - How to change GL Calendar from a Fiscal to a Calendar year?
NOTE:1062487.6 - Do you need to define entire calendar year if you will only use the last period?
NOTE:130539.1 - General Ledger Calendar Setup and Usage
NOTE:157642.1 - How to Change the First Accounting Open Period
NOTE:76503.1 - Scripts to Check the Setup of the General Ledger Calendar
Read more: http://prasanthapps.blogspot.com/search/label/General%20Ledger%20Calendar%20FAQ%20%5BID%20134362.1%5D#ixzz1pqIgfHVM
====================
Get On_Hand Quantities through API
Get On_Hand Quantities through API
This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve
You can also get the On-hand quantities from the table mtl_onhand_quantities
CREATE OR REPLACE function APPS.XX_TP_GET_ITEMS_TEST(v_organization_id in number,
v_inventory_item_id in number
)
return number
as
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2(1000);
--v_inventory_item_id VARCHAR2(250) := '64';
--v_organization_id VARCHAR2(10) := '12';
Cursor c_item_info is
select msb.INVENTORY_ITEM_ID,msb.ORGANIZATION_ID
from
MTL_SYSTEM_ITEMS_B msb
where
msb.ORGANIZATION_ID=nvl(v_organization_id,msb.ORGANIZATION_ID)
and msb.INVENTORY_ITEM_ID=nvl(v_inventory_item_id,msb.INVENTORY_ITEM_ID);
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);
For i in c_item_info
LOOP
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => i.ORGANIZATION_ID,
p_inventory_item_id => i.INVENTORY_ITEM_ID,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => APPS.INV_QUANTITY_TREE_PVT.g_all_subs, --3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr);
DBMS_OUTPUT.put_line ('on hand Quantity :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);
return v_qty_oh;
end loop;
exception
when others then
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Read more: http://prasanthapps.blogspot.com/search/label/Get%20On_Hand%20Quantities%20through%20API#ixzz1pqIkPMhy
=================
Get the Cost of an Item (API)
API to Get the Cost of an Item
Group Team
Below code can be used to get the cost of an Item.
The function returns the item cost for a given item and the organization.
All the parameter used in the Api are IN Parameter. Cost_group_id and Cost_type_id have the default value as NULL.
We can pass cost_group_id or cost_type_id, if you desire most specific cost Information. Do not pass both the cost group and Cost type at the same time.
API can fail for the below scenario
Invalid item/organization combination.
Item is not cost enabled.
Item has no cost in the specified cost group or cost type.
DECLARE
v_inventory_item_id NUMBER := 832215;
v_organization_id NUMBER := 209;
v_api_version NUMBER := 1.1;
v_cost_type_id NUMBER;
v_item_cost NUMBER := 0;
v_cost_group_id NUMBER;
BEGIN
dbms_output.put_line('API CST_COST_API to get the Item Cost);
v_item_cost := CST_COST_API.get_item_cost
(p_api_version => v_api_version,
p_inventory_item_id => v_inventory_item_id,
p_organization_id => v_organization_id,
p_cost_group_id => v_cost_group_id,
p_cost_type_id => v_cost_type_id
);
IF v_item_cost is NULL THEN
dbms_output.put_line('Issue in getting the Item Cost and Issue can be any one specified in the error scenario of the post');
ELSE
dbms_output.put_line('Item Cost is ' || v_item_cost);
END IF;
EXCEPTION
WHEN
OTHERS
THEN
dbms_output.put_line('Error in calling api and the error is ' ||
substr(SQLERRM,
1,
200));
END;
Read more: http://prasanthapps.blogspot.com/search/label/Get%20the%20Cost%20of%20an%20Item%20%28API%29#ixzz1pqIoBlkR
==============
HOW TABLES ARE EFFECTED
GL Lession
Maintenance and Relevant ReportsCalendar Maintenance:
GL Can't be started without defining the first open period from the Open/Close Calendar option. One has to be very careful when selecting the first period as once opened a period prior to that can't be opened. Once opened, on an ongoing basis periods have to be opened and closed. New Years whould be required to be appended to the existing calendars.
Account Values Maintenance:
On a regular basis lot of values would have to be opened, disabled etc.
Use Mass Maintenance to move balances by period from one account to another or merge balances by period from multiple accounts into a single account. The moved/merged balances are added to the existing balances in your target accounts. To change a move/merge, one can reverse it and restore account balances to their previous amounts.
During a move/merge operation the financial integrity between GL and its sub ledgers is maintained.
Document Sequencing:Every time the validity of the Document sequence is over, define and assign new sequence.
Currency Rate Maintenance
Standard Reports:
Account Analysis, Trial Balance (Detailed, Summary, Expanded), Budget Reports, Chart of Accounts Reports and
Listings, Currency Listings Reports, FSG Reports on Row/Column Set Details, GL Report, Journal Reports and
Execution Reports.
Closing Procedure:
Set the status of the first accounting period in the new fiscal year to Future Entry.
If the business rules require reversing entries at the beginning of every period, generate and post accruals from the prior period. If prior period reversals were not generated and posted at the beginning of this period, then generate reversals
Transfer data from all of sub ledgers and feeder systems to the GL_INTERFACE table. Review and Post the imported journal entries. Close the period for each sub ledger. This prevents future sub ledger transactions from being posted to GL in the same period. Perform reconciliations of subsidiary ledgers by reviewing and correcting balances.
Generate all recurring journals and step–down allocations.
Revalue balances to update foreign currency journals to functional currency equivalents.
Post all journal entries, including: manual, recurring, step–down allocations, and reversals.
Update any unpostable journal entries and then post them again.
Run GL reports, such as the Trial Balance reports, Account Analysis reports, and Journal reports.
Translate balances to any defined currency if report in foreign currencies is required.
Consolidate subsidiary SOBs in case of multiple companies.
If using a calendar with an adjusting period that represent the last day of the fiscal year, close the current period and open the adjusting period. Create and post adjusting entries and accruals in the adjusting period.
Run Trial Balance reports and other GL Reports in the adjusting period after adjustments are made.
If it is required to have an actual closing journal entry that shows the closing of income statement accounts to retained earnings, submit the Create Income Statements Closing Journals program. This program creates an auditable closing journal entry. The income statement will reflect zero balances on posting
If local accounting rules require balance sheet to be closed, submit the Create Balance Sheet Closing Journals program. Balance sheet will now reflect zero balances on posting.
Close the last period of the fiscal year and Open the first period of the new fiscal year to launch a concurrent process to update account balances. Opening the first period of a new year
automatically closes income statement and posts the difference to retained earnings account specified in the SOB form.
Perform Year–End Encumbrance Procedures (if applicable).
Run FSG reports for the last period of the year.
If balance sheet is closed at year–end, reverse the Balance Sheet Closing Journals to repopulate balances of balance sheet accounts for the new year.
Read more: http://prasanthapps.blogspot.com/search/label/HOW%20TABLES%20ARE%20EFFECTED#ixzz1pqIsUunr
============
GL Tables, AP Tables,HZ TABLES, AR TABLES AND FLOW DIAGRAM,FND TABLES
GL Tables
General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each category.
Ledgers Tables:
GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information.
GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application.
Period Tables:
GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar form.
GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.
GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the period type name, the number of periods per fiscal year, and other information.
Journal Tables:
GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information.
GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry.
GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line.
GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. This table corresponds to the Journal Sources form.
GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.
Conversion and consolidation tables:
GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a mapping’s ID, name, description, and other information. This table corresponds to the first window of the Consolidation Mappings form. You need one row for each consolidation mapping you define.
GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of the Transfer Consolidation Data form.
GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type.
GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in sets of books with average balances enabled.
Budgeting tables:
GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one budget type, ‘STANDARD’. Therefore, this table always contains only one row.
GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget Organization form.
GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this table, and then deletes the rows in this table that it used.
Interface Tables:
GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches.
GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. When Journal Import completes, it deletes these rows from the table.
GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget amounts for an account.
PAYABLES TABLES
AP_SUPPLIERS:
This table replaces the old PO_VENDORS table.
It stores information about your supplier level attributes.
Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers.
The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.
AP_SUPPLIER_SITES_ALL:
This table replaces the old PO_VENDOR_SITES_ALL table.
It stores information about your supplier site level attributes.
There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
Each row includes the supplier reference, purchasing, invoice, and general information.
AP_INVOICES_ALL:
It contains records for invoices you enter.
There is one row for each invoice you enter.
An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.
AP_INVOICE_LINES_ALL:
It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
An invoice can have one or more invoice lines.
An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.
AP_INVOICE_DISTRIBUTIONS_ALL:
It holds the distribution information that is manually entered or system-generated.
There is one row for each invoice distribution and a distribution must be associated with an invoice.
An invoice can have multiple distributions.
AP_INVOICE_PAYMENTS_ALL:
It contains records of invoice payments that you made to suppliers.
There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.
AP_PAYMENT_SCHEDULES_ALL:
This table stores information about scheduled payment information on invoices.
AP_PAYMENT_HISTORY_ALL:
It stores the clearing/unclearing history for payments.
It also stores the maturity history for future dated payments.
The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.
AP_BATCHES_ALL:
It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
There is one row for each batch of invoices you enter.
If you enable Batch Control, each invoice must correspond to a record in this table.
Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.
AP_CHECKS_ALL:
It stores information about payments issued to suppliers or refunds received from suppliers.
There is one row for each payment you issue to a supplier or refund received from a supplier.
Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.
AP_HOLDS_ALL:
It contains information about holds that you or your Oracle Payables application place on an invoice.
For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
An invoice may have one or more corresponding rows in this table.
Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.
AP_BANK_ACCOUNTS_ALL:
It contains information about your bank accounts.
There is one row for each bank account you define and each bank account must be affiliated with one bank branch.
AP_BANK_ACCOUNT_USES_ALL:
It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.
AP_CARDS_ALL:
It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.
AP_TRIAL_BALANCE:
It contains denormalized information about invoices and payments posted to the accrual set of books.
HZ(TCA) tables in Oracle Receivables
This article describes few important HZ tables in AR and their relationships with each other.
HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.
Few Important Columns are
PARTY_ID: Party identifier
PARTY_NUMBER: Unique identification number for this party
PARTY_NAME: Name of the party
PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.
HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are
PARTY_SITE_ID: Party site identifier.
PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
PARTY_SITE_NUMBER: Party site number.
PARTY_SITE_NAME: User-defined name for the site.
ADDRESSEE: Addressee information.
HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.
Few Important Columns are
LOCATION_ID: Unique identifier for this location
COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
ADDRESS1: First line for address
ADDRESS2: Second line for address
ADDRESS3: Third line for address
ADDRESS4: Fourth line for address
CITY: City
POSTAL_CODE: Postal Code
STATE: State
ADDRESS_KEY: Derived key that facilitates fuzzy searches
HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.
Few Important Columns are
CUST_ACCOUNT_ID: Customer account identifier
PARTY_ID: A foreign key to the HZ_PARTY table.
ACCOUNT_NUMBER: Account Number
CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
CUSTOMER_CLASS_CODE: Customer class identifier
HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.
Few Important Columns are
CUST_ACCT_SITE_ID: Customer site identifier
CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
BILL_TO_FLAG: Indicates if this is a Bill-To site.
SHIP_TO_FLAG: Indicates if this is a Ship-To site.
MARKET_FLAG: Indicates if this is a Marketing site.
HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.
Few Important Columns are
SITE_USE_ID: Site use identifier
CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.
HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are
CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
STATUS: Indicates whether the profile is active or inactive
HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.
HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.
AR Tables:A Diagrammatic Relation
Key FND Tables in Oracle Application
Here there are few key FND tables that we use in our AOL queries.
FND_APPLICATION:
Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL:
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS:
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES:
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS:
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS:
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL:
Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES:
Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE:
Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS:
Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS:
Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS:
This table stores output files created by Concurrent Request.
FND_CURRENCIES:
Stores information about currencies.
FND_DATABASES:
It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES:
Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS:
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL:
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS:
Stores language-independent information about a document.
FND_EXECUTABLES:
Stores information about concurrent program executables.
FND_FLEX_VALUES:
Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS:
Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES:
Stores information regarding languages and dialects.
FND_MENUS:
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL:
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES:
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS:
Stores information about user profile options.
FND_REQUEST_GROUPS:
Stores information about report security groups.
FND_REQUEST_SETS:
Stores information about report sets.
FND_RESPONSIBILITY:
Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL:
Stores translated information about responsibilities.
FND_RESP_FUNCTIONS:
Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS:
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES:
Stores information about the registered sequences in your applications.
FND_TABLES:
Stores information about the registered tables in your applications.
FND_TERRITORIES:
Stores information for countries, alternatively known as territories.
FND_USER:
Stores information about application users.
FND_VIEWS:
Stores information about the registered views in your applications.
Read more: http://prasanthapps.blogspot.com/search/label/HZ%20TABLES#ixzz1pqIyg6yH
==============
Hard Reservation for Standard Sales Order line against Purchase Order
Hard Reservation for Standard Sales Order line against Purchase Order:-
-------------------------------------------------------------------------------------
Note:- You need to have the ASCP Module Installed for the following script to work.
Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.
When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.
Here, we are considering if ASCP has decided to create the Purchase Order.
For that you can use the following script.
/************************************************************
Hard Reservation for Standard Sales Order line against Purchase Order
Created by : PRASANTH
Creation Date :
Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.
For others, the input values can be passed directly without CURSOR.
************************************************************/
PROCEDURE create_po_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)
IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;
CURSOR C_PO_RESV(c_line_id) ----- Cursor to select Purchas Orders pegged to the standard sales order line
IS
SELECT
DISTINCT pla.PO_HEADER_ID,
pda.PO_DISTRIBUTION_ID,
mfp.ALLOCATED_QUANTITY
FROM PO_LINES_ALL pla,
PO_HEADERS_ALL pha,
PO_DISTRIBUTIONS_ALL pda,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND pla.PO_HEADER_ID=pha.PO_HEADER_ID
AND pla.PO_HEADER_ID=pda.PO_HEADER_ID
AND pla.PO_LINE_ID = pda.PO_LINE_ID
AND pla.PO_HEADER_ID=ms.DISPOSITION_ID
AND pla.PO_LINE_ID= ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;
BEGIN
--- initialize required input params to call reservation api--
--- get the item id--
SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);
--- get the sales_order_id from mtl_sales_orders
--- table which will be passed as one of the input params-
SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);
FOR C_REC IN C_PO_RESV(p_line_id)
-------------- sending the sales order line to the cursor variable
LOOP
----- Initialize al required inputs to perform HARD RESERVATION------
--fnd_global.APPS_Initialize(2572700,20559,300);
--p_user_id, p_resp_id, p_resp_appl_id
p_rsv.reservation_id := NULL; -- cannot know
------------will be generated once reservation is successful
p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id;
---------------------------mtl_parameters.organization id
p_rsv.inventory_item_id := l_inventory_item_id;
---------------------------mtl_system_items.Inventory_item_id
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;-------'SALES ORDER';
p_rsv.demand_source_header_id :=l_source_header_id;
--------------------mtl_sales_orders.sales_order_id for order number
p_rsv.demand_source_line_id := p_line_id;
-------------------- oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_po;
p_rsv.supply_source_header_id :=C_REC.PO_HEADER_ID;
------------------------Header id of PO
p_rsv.supply_source_line_id :=C_REC.PO_DISTRIBUTION_ID;
------------------------Distribution id of PO
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;
-------------subinventory code can be mentioned
p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;--17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;--'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;
------------------------------ CASE II-------------------------------------------
--- Validating whether the pegging is done WITH AN EXTERNAL PURCHASE ORDER---
---- FInally performing Hard Reservation ---------
inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);
IF x_status='S' THEN --- HARD RESERVATION IS DONE SUCCESFULLY ---
COMMIT;
dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);
ELSE
if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;
--- HARD RESERVATION API fails ---
dbms_output.put_line('Reservation API Error Message: '||l_error_message);
END IF;
EXCEPTION
WHEN OTHERS THEN ------------------ In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);
END create_po_hard_reservation;
Read more: http://prasanthapps.blogspot.com/search/label/Hard%20Reservation%20for%20Standard%20Sales%20Order%20line%20against%20Purchase%20Order#ixzz1pqJ4KC8r
===============
Customer TCA Architecture And API
Customer TCA Architecture And API
Overview:Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables.
Architecture
Customer_TCA_Architecture_API
Create Organization
DECLARE
p_organization_rec hz_party_v2pub.organization_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
BEGIN
p_organization_rec.organization_name := 'erpschools';
p_organization_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_v2pub.create_organization ('T',
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id
);
DBMS_OUTPUT.put_line ('party id ' || x_party_id);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables.
Create a Location
DECLARE
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_location_rec.country := 'US';
p_location_rec.address1 := '2500 W Higgins Rd';
p_location_rec.address2 := 'Suite 920';
p_location_rec.city := 'Thumuluru';
p_location_rec.postal_code := '60118';
p_location_rec.state := 'IL';
p_location_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_location_v2pub.create_location(
'T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('location id '||x_location_id);
dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END
Note: The above API shall create an address record in hz_locations table.
Create a Party Site:
Use the organization_id and location_id created above and create a party site.
DECLARE
p_party_site_rec hz_party_site_v2pub.party_site_rec_type;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2 (2000);
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_party_site_rec.party_id := 1272023;
p_party_site_rec.location_id := 359086;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line ('party site id ' || x_party_site_id);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_party_sites table.
Create Party Site Use
Use the above party site created
DECLARE
p_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
x_party_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_party_site_use_rec.site_use_type := 'SHIP_TO';
p_party_site_use_rec.party_site_id := 349327;
p_party_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_site_v2pub.create_party_site_use ('T',
p_party_site_use_rec,
x_party_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create a Contact Point
DECLARE
p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
p_edi_rec hz_contact_point_v2pub.edi_rec_type;
p_email_rec hz_contact_point_v2pub.email_rec_type;
p_phone_rec hz_contact_point_v2pub.phone_rec_type;
p_telex_rec hz_contact_point_v2pub.telex_rec_type;
p_web_rec hz_contact_point_v2pub.web_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_contact_point_id NUMBER;
BEGIN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := '1272023';
p_contact_point_rec.primary_flag := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_phone_rec.phone_area_code := '650';
p_phone_rec.phone_country_code := '1';
p_phone_rec.phone_number := '506-7000';
p_phone_rec.phone_line_type := 'GEN';
p_contact_point_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_contact_point_v2pub.create_contact_point ('T',
p_contact_point_rec,
p_edi_rec,
p_email_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create an Org Contact:
DECLARE
p_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_org_contact_rec.department_code := 'ACCOUNTING';
p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
p_org_contact_rec.decision_maker_flag := 'Y';
p_org_contact_rec.job_title_code := 'APC';
p_org_contact_rec.created_by_module := 'ERPSCHOOLS_DEMO';
p_org_contact_rec.party_rel_rec.subject_id := 16077;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 1272023;
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
x_org_contact_id,
x_party_rel_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as 'PARTY_RELATIONSHIP'.
Create a Customer Account:
DECLARE
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_person_rec hz_party_v2pub.person_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profilerec_type;
x_cust_account_id NUMBER;
x_account_number VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_cust_account_rec.account_name := 'John''s A/c';
p_cust_account_rec.created_by_module := 'ERPSCHOOLS_DEMO';
p_person_rec.person_first_name := 'John';
p_person_rec.person_last_name := 'Smith';
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F',
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note:
This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.
Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.
Create a Customer Account Site
Use an existing Party Site
DECLARE
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_cust_acct_site_id NUMBER;
BEGIN
p_cust_acct_site_rec.cust_account_id := 3472;
p_cust_acct_site_rec.party_site_id := 1024;
p_cust_acct_site_rec.LANGUAGE := 'US';
p_cust_acct_site_rec.created_by_module := 'TCA-EXAMPLE';
hz_cust_account_site_v2pub.create_cust_acct_site ('T',
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create Customer Account Site Use Code:
DECLARE
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_cust_site_use_rec.cust_acct_site_id := 3580;
p_cust_site_use_rec.site_use_code := 'INV';
p_cust_site_use_rec.LOCATION := 'TCA';
p_cust_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_cust_account_site_v2pub.create_cust_site_use ('T',
p_cust_site_use_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
More Customer API's:
Org Contact Role
Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile
HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount
HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating
HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person
JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories
JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts
HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
Read more: http://prasanthapps.blogspot.com/search/label/Customer%20TCA%20Architecture%20And%20API#ixzz1pqJILoR3
===============
Customer Tables Understanding
Customer Tables Understanding
Hello everyone,
This is a just how customer informations are stored in apps tables. This post gives you clear explanation of how customers information are getting stored.
Customer Created Name: TESTCUST_1
Customer Accounts Created under the customer TESTCUST_1:
1.TESTCUST_1_1
2.TESTCUST_1_2
For Customer:
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTCUST_1'
Party Id: 307957
For Customer Accounts:
SELECT * FROM HZ_CUST_ACCOUNTS hca
WHERE hca.ACCOUNT_NAME IN ('TESTCUST_1_1','TESTCUST_1_2')
Account Number: 5391,5392
Account Id: 96675,96677
Customer Accounts Sites:
Under the Customer Account:
1.TESTCUST_1_1
Sites Name:
1.TESTCUSTS1_1
2.TESTCUSTS1_2
/*2.TESTCUST_1_2
Site Name:
1.TESTCUSTS2_1
2.TESTCUSTS2_1*/
SELECT * FROM HZ_CUST_ACCT_SITES_ALL hcsa
where hcsa.CUST_ACCOUNT_ID IN (96675) -- Customer Account Id of TESTCUST_1_1 ---Sites - TESTCUSTS1_1,TESTCUSTS1_2
Customer Account Site Id: 10554 - TESTCUSTS_1_2
10558 - TESTCUSTS_1_1
SELECT * FROM HZ_PARTY_SITES
WHERE PARTY_ID = 307957 -- Check Party Site Name : TESTCUSTS1_1,TESTCUSTS1_2
Party site Id: 179402 - TESTCUSTS_1_2
179404 - TESTCUSTS_1_1
For the Site TESTCUSTS1_1:
Contact Creation:
1.Contact Name: TESTS_1_1
2.Telephone - created
3.Email
There are two Id need to get, one is subject id and other is object id
1.Subject Id is related to Organization that is customer Party id here the party id is
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTCUST_1'
Party Id: 307957 - subject Id
2.Object Id is related to the contact name created that is Party Id of the person(Contact)
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTS_1_1'
Party Id : 307959 - Object id
Now Check in the Relationship table using subject id and object id
SELECT * FROM HZ_RELATIONSHIPS hr
WHERE hr.SUBJECT_ID = 307957
and hr.OBJECT_ID = 307959
and the reverse entry will also be there
SELECT * FROM HZ_RELATIONSHIPS hr
WHERE hr.SUBJECT_ID = 307959
and hr.OBJECT_ID = 307957
Relatonship Id:137025
Party Id:307960
SELECT * FROM HZ_PARTIES hp
WHERE hp.PARTY_ID = 307960 -- Party_type will be PARTY_RELATIONSHIP
SELECT * FROM HZ_ORG_CONTACTS hoc
where hoc.PARTY_RELATIONSHIP_ID = 137025 -- Relatioship Id
Org Contact Id: 118071
SELECT * FROM HZ_CONTACT_POINTS hp --HZ_CUST_CONTACT_POINTS hcp
WHERE hp.OWNER_TABLE_ID = 179404 --Party Site Id of the Contact - TESTCUSTS_1_1
SELECT * FROM HZ_CUST_ACCOUNT_ROLES hcr
WHERE hcr.CUST_ACCT_SITE_ID = 10558 --Customer Account Role created when creating contacts --This is the Customer Site Id of TESTCUSTS_1_1
===========
There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:
CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);
SELECT * FROM dup_test;
Use subquery to delete duplicate rows:
Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:
You can also detect and delete duplicate rows using Oracle analytic functions:
DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:
This is the simplest method to remove duplicity.
CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:
This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:
DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.
Read more: http://prasanthapps.blogspot.com/search/label/Delete%20Duplicate%20Records%20in%20Oracle#ixzz1pqHceEgU
===============
Delete concurrent program from backend
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;
Read more: http://prasanthapps.blogspot.com/search/label/Delete%20concurrent%20program%20from%20backend#ixzz1pqHgjiNP
===========
Difference Between Lexical and Bind Variable
Difference Between Lexical and Bind Variable
Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &ATABLE
===
Difference between "Internal" and "External" Drop-Ship
Difference between "Internal" and "External" Drop-Ship
In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN).
You should take a note,Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed.
where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.
Here is Functional test cases for Drop shipment
1)Enter an order for drop ship item
Responsibility: Order Management
Orders, Returns -> Order Organizer -> New
Under Main tab:
Enter Customer,Order Type and Price List. Verify that any defaulting takes place per rules setup.
Under Line Items tab:
Enter item (must be Purchasable), qty, schedule ship date. Ensure Selling Price populates correctly.
Under the Shipping tab:
Enter Source Type = External
Enter Receiving Org
Save the order.
2)Book the order
Hit the Book Order button.
The order header status should show Booked.
The order line status should be Awaiting Receipt.
This can be verified by viewing the Status field on the sales order line, or by going to
Tools -> Workflow Status
Note: If the line status does not show Awaiting Receipt, try manually progressing the order via:
Actions -> Progress Order
3)Run Requisition Import
Orders, Returns -> Requisition Import
Enter Import Source = Order Entry
Submit the request and verify the sales order. Check for Sales Order is updated with the req number or not by opening the order and going to the Line Items tab. Select Actions -> Additional Line Information
Under the Drop Ship tab you will see the requisition information.
4)Create a purchase order from the requisition
within Responsibility: Purchasing
Go to Autocreate
In the Find Requisition Lines window, enter the requisition number, clear the buyer and ship to fields and click the Find button.In the Autocreate Documents window, select the requisition line and click the Automatic button. In the New Document window,select the supplier and click the Create button. Record the PO number.
5)Approve the PO
When you are able to find Purchase Order click the Approve button.Ensure the Submit for Approval box is checked and click OK.
6)Receive against the PO
Navigate to :Receiving -> Receipts
Once you select organization, find the details by passing PO number.Now Tab through the Receipt Header window to the Lines window.Complete the details.
7)Initiate Receiving Transaction
Receiving -> Receiving Transactions Summary, In the Find Expected receipts form, enter the PO number and hit Find button.
You need to check the box to the far left of the Receiving line and enter a Subinventory.Save and verify that the concurrent program Receiving Transaction Processor completes successfully.
Verify the transactions
Receiving -> Transactions -> Summary
Under Supplier and Internal tab, enter receipt number and hit Find button to get the details.
9)Now Verify Sales Order status updated
Responsibility: Order Management
Orders, Returns -> Order Organizer
Find details for order number and navigate to the Lines tab, find out the detail for status , it should be 'Shipped'.Shipped Qty should be updated to reflect the full quantity ordered.
(This test cases for drop shipment flow Adopted from Metalink on reader demand.)
Similar Post on Drop Shipment
Understand “Drop Shipment” in Order Management?
Understand “Drop Shipment” in Order Management? -Part II
‘Drop Shipment’, ‘BackOrders’ and ‘Back to Back Order’ …
Drop Shipment - Functional Setup and flow
"Internal" versus "External" Drop-Ship: What’s the difference?
=============
Difference between Party and Customer
Difference between Party and Customer
Buzz It
PARTY
CUSTOMER
Prospective Customer and more relevant for CRM Purposes
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).
No Business Transactions involved (Sales Order, Sales Invoice, Debit Memo, Credit Memo, Receipt etc.,)
A Business Transaction like a Sales Order, Invoice, Debit Memo, Credit Memo,Receipt can be created.
A Party does not have account but have Sites
A Customer will have account and as well as Sites.
A Party can exist without Customer Record
A Party record is must to create a Customer Record linked through party_id.
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
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.
===========
Duplicate Vendor List
Duplicate Vendor List
select pv1.vendor_name,
pv2.vendor_name DUP_VENDOR_NAME,
pv1.segment1 VENDOR_ID,
pv2.segment1 DUP_VENDOR_ID,
pvsa1.vendor_site_code SITE_CODE,
pvsa2.vendor_site_code SITE_CODE,
pvsa1.address_line1,
pvsa2.address_line1 DUP_ADDRESS_LINE1,
pvsa1.zip
from po_vendors pv1,
po_vendors pv2,
po_vendor_sites_all pvsa1,
po_vendor_sites_all pvsa2
where pvsa1.vendor_site_id <> pvsa2.vendor_site_id
and substr(replace(pvsa1.address_line1, ' '),1,20) =
substr(replace(pvsa2.address_line1, ' '),1,20)
and pvsa1.zip = pvsa2.zip
and pv1.vendor_id = pvsa1.vendor_id
and pv2.vendor_id = pvsa2.vendor_id
and pv1.vendor_id <> pv2.vendor_id
and pvsa1.address_line1 <> 'YOUR ADDEDSS’
order by 1;
================
Duplicate rows in the table
Duplicate rows in the table:-
--------------------------------
The following query can be used to get the duplicate records from table.
SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY'Your duplicate values field name');
Example:-
SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);
To eliminate/delete the duplicate rows from the table, you can use the following query.
DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');
Example:-
DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);
===========
Example of $FLEX$ Syntax
Example of $FLEX$ Syntax
Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:
Segment Name Manufacturer
Value Set Name Car_Maker_Name_Value_Set
Validation Table CAR_MAKERS
Value Column MANUFACTURER_NAME
Description Column MANUFACTURER_DESCRIPTION
Hidden ID Column MANUFACTURER_ID
SQL Where Clause (none)
Segment Name Model
Value Set Name Car_Model_Name_Value_Set
Validation Table CAR_MODELS
Value Column MODEL_NAME
Description Column MODEL_DESCRIPTION
Hidden ID Column MODEL_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set
Segment Name Color
Value Set Name Car_Color_Name_Value_Set
Validation Table CAR_COLORS
Value Column COLOR_NAME
Description Column COLOR_DESCRIPTION
Hidden ID Column COLOR_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set
AND MODEL_ID =
:$FLEX$.Car_Model_Name_Value_Set
In this above example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.
Read more: http://prasanthapps.blogspot.com/search/label/Example%20of%20%24FLEX%24%20Syntax#ixzz1pqI3VgkB
==========
FAQs in Account Payable
FAQs in Account Payable
How many types of Invoices we can create in Oracle Payables?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice
Describe the 4-Way Matching?
2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)
3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)
4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)
2-Way (Invoice to Purchase Order)
Quantity billed vs. quantity ordered on shipment line
Invoice unit price vs. purchase order line unit price
3-Way (Invoice to Purchase Order and Receipt)
2-Way match criteria AND
Quantity billed vs. quantity received
Item = Receipt Required
4-Way (Invoice to Purchase Order and Receipt and Inspection)
3-Way match criteria AND
Quantity billed vs. quantity accepted
Note: Quantity accepted = (Quantity received – quantity rejected)
Item = Inspection Required
How many Holds we have?
System Holds: Tax, Quantity Match, Po amount with Invoice Amount
Manual Holds: Invoice Limit, Hold on Invoice
Can you Release Manual Holds? If Yes, How?
Yes. Holds – Release Holds
How many ways you can pay the Invoice Amount?
Apply in Full
Schedule Payments
Installments
What is the difference between Debit and Credit Memo?
Debit Memo will raise the Customer
Credit Memo will raise the Vendor
Can you create a Duplicate Vendor ?
No
Vendor Number Should be Generate Automatic or Manually?
Both, Manual And Automatic
How many key flexfields are there in Payables?
No key flexfields in PO,AP
What are Payment Terms?
Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.
What are the Payment Methods available?
• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.
What are the line types of an Invoice?
Item: Item lines capture the details of the goods and services billed on your invoice.
Freight: Freight lines capture the details of your freight charges.
Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.
Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.
What are the Invoice Distribution Types?
Item
Freight
Miscellaneous
Tax
Withholding
Prepayment
What are Holds in AP?
Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.
What are the Interface Tables in AP?
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS
What is the API to cancel single AP Invoice?
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
What is the API to find invoice status?
AP_INVOICES_PKG.GET_APPROVAL_STATUS
Give some of the Oracle Payables interface?
a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching
What are the key tables where supplier bank information are stored?
IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL
What is meant by with-holding tax invoice?
A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.
What are all the Modules Interacting with AP?
Cash Management
Oracle iExpenses
General Ledger
Oracle Assets
Subledger Accounting (R12)
HRMS
Project Accounting
Purchasing/iprocurement
Global Accounting Engine (11i)
What are the mandatory setups in AP?
1- Financial Options
2- Define Suppliers
3- Define Payment Terms
4- Define Payment Methods
5- Define Banks and Banks Accounts And Banks Accounts Documents
6- Open AP Accounts Periods
What is pay date basis?
The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.
• Due
• Discount
What is the difference between quick payment and manual payment?
Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.
Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.
What is terms date basis?
Terms Date Basis is to calculate due date.
Due date is calculated 4way. Eg: payment term is 30days
Due date = Sysdate + 30days
Due date = Invoice date + 30days
Due date = Goods Receive Date + 30days
Due date = Invoice Received date + 30days
How you will transfer payables to general ledger?
When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.
What are Aging Periods?
Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.
What is a “Mixed” Invoice and how do I enter one?
Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.
For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.
To enter a Mixed invoice:
1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.
2. Match to purchase orders, and/or invoices
Read more: http://prasanthapps.blogspot.com/search/label/FAQs%20in%20Account%20Payable#ixzz1pqI7YTFq
===
FINANCIALS Interview Questions
FINANCIALS Interview Questions
Q: What are the 4 C's in Accounting?
A: In Oracle 11i 3c's i.e Currency Calendar Char of Account
In R12 4c's include sub-ledger account along the above
!
Q: What is delegation in iExpense?
A: You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate.
Q: What is the process/steps for Vendor Conversion?
A: Insert the Vendor info into the interface tables and perform the required validations:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
Run the below programs to load the data into the Base tables:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
Q: What is Debit Memo & Credit Memo in Payables?
A: Credit Memo is a negative amount invoice you receive from a supplier representing a credit. Debit Memo is a negative amount invoice you send to notify a supplier of a credit you recorded for goods or services purchased.
Q: Explain the set up used for Automatic or Manual Supplier Numbering.
A: In the Financials Options window, you can set the Supplier Number entry option to either Autimoatic or Manual • Automatic: The system automatically assigns a unique sequential number to each supplier when you enter a new supplier. • Manual: You enter the supplier number when you enter a supplier
Q: What is Contract PO?
A: Contract PO is created when you agree with your suppliers on specific terms and conditions without indicating the goods and services that you will be purchasing.
Q: What is a Payable Document?
A: A medium you use to instruct your bank to disburse funds from your bank account to the bank account or site location of a supplier.
Q: In which table we can find the vendor number?
A: PO_VENDORS
Q: Give the cycle from creating an invoice to transferring it to GL in AP.
A: 1)Create Invoice 2)Validate Invoice 3)Create Accounting entries using Payables Accounting Process 4)Submit the Payables Transfer to General Ledger program to send invoice and payment accounting entries to the General Ledger interface. 4)Journal Import (GL) 5)Journal Post (GL)
Q: What are the different types of Invoices in Payables?
A: Standard, Credit Memo, Debit Memo, Expense Report,PrePayment, Mixed, PO Default
Q: You have created a new SOB. How will you attach this SOB to AP?
A: Go to Payables Manager for the appropriate Operating Unit.
Navigation:Setup--->Set of Books--->choose.
Q: How many key flexfields are there in Payables?
A: No Key Flexfields in AP
Q: What is the Distribution Type while entering the Invoice?
A: Item, Tax, Miscellaneous,Freight, Withholding Tax
Q: What are the Prepayment types?
A: Temporary and Permanent
Q: What is Aging Periods?
A: Aging Periods window are the time periods for the Invoice Aging Report. The Invoice Aging Report provides information about invoice payments due during four periods you specify.
Q: Whats the difference between the "Payables Open Interface Import" Program and the "Payables Invoice Import" program?
A: Payables Open Interface -- for importing regular invoices Payables Invoice Import -- for importing expense reports. In 11i renamed as Expense Report Import.
Q: What is prepayment & steps to apply it to an Invoice?
A: Prepayment is a type pf invoice that you enter to make an advance
payment to a supplier or employee.
To Apply it to an Invoice ,in the Invoices window, query either the prepayment or the invoice to which you want to apply it. Choose the Actions button and select the Apply/Unapply Prepayment check box. Click OK.
Q: Can you hold the partial payment if yes then how?
A: Yes.
1.Go to the Invoice window. Go to the scheduled payments tab.
2.Click "Split" to split the scheduled payment into as many
payments as you wish.
3.Check "Hold" against the Payment line you wish to hold.
Q: How you will transfer payables to general ledger?
A: Create Accounting. Transfer the transactions to GL_Interface Import the Journals Post the Journals
Q: What program is used to transfer AP transactions to GL?
A: Payables Transfer to General Ledger Program
Q: What is use of AP Accounting Periods?
A: In Payables accounting periods have to be defined to enter and account for transactions in these open periods. Payables does not allow transaction processing in a period that has never been opened. These periods are restricted to Payables only. The period statuses available in Payables are Never Opened, Future,Open, Closed, and Permanently Closed.
Q: What are the different interface programs in AP?
A: Payables Open Interface Import to load Invoices and other transactions.
Supplier Open Interface Import to load Suppliers.
Supplier Sites Open Interface Import to load Supplier sites.
Supplier Site Contacts Open Interface Import to load Supplier Site contacts.
Q: What is Invoice Tolerance?
A: We can define the matching and tax tolerances i.e how much to allow for variances between invoice, purchase order, receipt, and tax information during matching. You can define both percentage–based and amount–based tolerances.
Q: What will accrue in Payables?
A: Expenses and Liabilities
Q: What is a Hold? Explain the types of Hold.
A: Payables lets you apply holds manually on an invoice, Payments etc to prevent the payment from being made or to prevent the accounting entries to be created etc. Some of the Payable holds are -- Invoice Hold, Accounts Hold, Funds Hold, Matching Hold, Variance Hold, Misc hold.
Q: Which module is the owner of Vendor/Supplier tables?
A: Purchasing
Q: What is Payment Terms?
A: Payment Terms let you define the due date or the discount date , due amount or discount amount. Once the payment terms are defined, you can attach these to the suppliers and supplier sites and these terms will be automatically populated once the invoice is entered for a supplier site.
Q: can we make a half payment in payables?
then how?
A: No answer yet!
Q: can we create invoice without PO in payables? then how?
A: No answer yet!
Q: In AP the suppliers didn’t visible in India Creditors Ledger Report Parameter?
A: pls check whether that particular supplier is available in Suppliers addition inforamtion or not.
Q: What kind of transactions can be created using AutoInvoice?
A: Invoices, credit memos, debit memos, and on–account credits can be imported using AutoInvoice.
Q: What are the underlying tables and validations required during AutoInvoice Interface?
A: Interface tables: RA_INTERFACE_LINES_ALL Base tables: RA_CUSTOMER_TRX_ALL RA_BATCHES RA_CUSTOMER_TRX_LINES_ALL AR_PAYMENT_SCHEDULES_ALL RA_CUSTOMER_TRX_LINE_SALESREPS RA_CUST_TRX_GL_DIST_ALL AR_RECEIVABLES_APPLICATIONS AR_ADJUSTMENTS RA_CUSTOMER_TRX_TYPES_ALL Concurrent Program: Auto invoice master program Validations: check for amount, batch source name, conversion rate, conversion type. Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity. Validate if the amount includes tax flag.
Q: Explain the different steps in implementing Autolockbox.
A: Import, Validate, Post Quick Cash
Q: What are the different Invoice matching types?
A: 2-way matching: 2-way matching verifies that Purchase order and invoice quantities must match within your tolerances 3-way matching: 3-way matching verifies that the receipt and invoice information match with the quantity tolerances 4-way matching: 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances
Q: What are the different Transaction types in AR?
A: Invoice, Credit Memo, Debit Memo,Charge back, commitments
Q: Tell me about TCA?
A: TCA canbe used to import or modify Customers related data.
Q: Name some Flexfields in AR.
A: Sales Tax Location, Territory
Q: Explain the steps involved in Transfer to GL from AR.
A: Transfer the transactions to GL_Interface Import the Journals Post the Journals
Q: What is the dbnumber of a particular cusotmer TCA?
A: It is a unique number used to identify the Customers.
Q: Where can you find the Customer payment terms?
A: In the table hz_customer_profiles
Q: What is the link between OM and AR?
A: To relate the Order Number (ONT) to the Invoice (AR) we use the LINE TRANSACTION FLEX FIELDS. In RA_CUSTOMER_TRX_ALL, INTERFACE_HEADER_ATTRIBUTE1 to INTERFACE_HEADER_ATTRIBUTE15 store this Information that uniquely identifies the Sales Order (HEADER INFO). In RA_CUSTOMER_TRX_LINES_ALL, INTERFACE_LINE_ATTRIBUTE1 to INTERFACE_LINE_ATTRIBUTE15 store this Information that uniquely identifies the Sales Order.(LINE INFO)
Q: how can we adjust the money in AR?
A: No answer yet!
Q: What is Blanket PO?
A: A Blanket PO is created 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 know the detail of your delivery schedules.
Q: What are different types of PO’s ?
A: Standard, Blanket, Contract, Planned
Q: How does workflow determines whom to send the PO for approval?
A: It Depends on the setup whether it is position based or supervisor based.
Q: Can you create PO in iProcurement ?
A: No
Q: Give me some PO tables
A: PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL,PO_DISTRIBUTIONS_ALL
Q: Tell me about PO cycle?
A: Create PO
Submit it for Approval
Receive Goods against the PO (when order is delivered by the vendor)
Close the PO after the invoice is created in AP and teh payments have been made to the vendor.
Q: Explain Approval Heirarchies in PO.
A: Approval hierarchies let you automatically route documents for approval. There are two kinds of approval hierarchies in Purchasing: position hierarchy and employee/supervisor relationships.
If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.
If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.
Q: What functions you do from iProcurement?
A: Create Requisition, Receive the goods.
Q: What is difference between positional hierarchy and supervisor hierarchy?
A: If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.
If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.
Q: What is the difference between purchasing module and iProcurement module?
A: Iprocurement is a self service application with a web shopping interface. WE can only create and manage requisitions and receipts.
Purchasing module is form based and also lets you create PO and many other functions are possible other than requisitions and receiving.
Q: What is dollar limit and how can you change it?
A: An approval group is defined which is nothing but a set of authorization rules comprised of include/exclude and amount limit criteria for the following Object Types: Document Total, Account Range, Item Range, Item Category Range, and Location that are required to approve a PO.
You can always change the rules by doing the below:
Navigation:
Purchasing Responsibility > Setup > Approvals > Approval Groups
Query the Approval group and change teh rules accordingly.
Q: What is Planned PO?
A: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
Q: What is Purchase order workflow ?
A: No answer yet!
Q: what are the tables link between PO & Gl technically?
A: No answer yet!
Q: What is backdated Receipt?
A: Creating a Receipt with "Receipt Date" less than sysdate or today's date is referred to as 'Backdated Receipt".
Q: Is it possible to create a backdated receipt with a receipt date falling in closed GL period?
A: No. The receipt date has to be with in open GL period.
Q: What is Receipt Date Tolerance?
A: The buffer time during which receipts can be created with out warning/error prior or later to receipt due date.
Q: How do we set the Receipt Tolerance?
A: Receipt Tolerance can be set in three different places.
1. Master Item Form (Item Level)
2. setup, organization form (Organization Level)
3. Purchase Order, Receiving controls. (shipment level).
Read more: http://prasanthapps.blogspot.com/search/label/FINANCIALS%20Interview%20Questions#ixzz1pqICa2Rs
================
FLEX FIELDS
FLEX FIELDS
Keyflexfields and the tables in which they store their code combinations.
AccountingFlexfield GL_CODE_COMBINATIONS
CategoryFlexfield FA_CATEGORIES_B
Asset KeyFlexfield FA_ASSET_KEYWORDS
LocationFlexfield FA_LOCATIONS
Oracle Service ItemFlexfield MTL_SYSTEM_ITEMS_B
TerritoryFlexfield RA_TERRITORIES
Sales Tax LocationFlexfield AR_LOCATION_COMBINATIONS
Item Categories MTL_CATEGORIES_B
Account Aliases MTL_GENERIC_DISPOSITIONS
Item Catalogs MTL_ITEM_CATALOG_GROUPS
Sales Orders MTL_SALES_ORDERS
System Items MTL_SYSTEM_ITEMS_B
Stock Locators MTL_ITEM_LOCATIONS
GradeFlexfield PER_GRADE_DEFINITIONS
JobFlexfield PER_JOB_DEFINITIONS
Personal AnalysisFlexfield PER_ANALYSIS_CRITERIA
PositionFlexfield PER_POSITION_DEFINITIONS
Soft CodedKeyFlexfield HR_SOFT_CODING_KEYFLEX
Bank DetailsKeyFlexField PAY_EXTERNAL_ACCOUNTS
Cost AllocationFlexfield PAY_COST_ALLOCATION_KEYFLEX
People GroupFlexfield PAY_PEOPLE_GROUPS
Descriptive Flexfield and the tables in which they store their code combinations.
Accounting Calendar: Calendar GL_PERIOD_SETS
Accounting Calendar: Periods GL_PERIODS
AutoAccounting Rules GL_IEA_AUTOGEN_MAP
AutoAllocationBatch GL_AUTO_ALLOC_BATCHES
AutoPostCriteria GL_AUTOMATIC_POSTING_OPTIONS
AutoReversalCriteria GL_AUTOREVERSE_OPTIONS
Automatic Posting Sets GL_AUTOMATIC_POSTING_SETS
Budget Types GL_BUDGET_TYPES
Budget Versions GL_BUDGET_VERSIONS
Budgetary Control Group: Group GL_BC_OPTIONS
Budgetary Control Group: Rules GL_BC_OPTION_DETAILS
Common Stocks: Share Activity GL_SHARES_ACTIVITY
Conversion Rate Types GL_DAILY_CONVERSION_TYPES
Value Set Usage by KeyFlexfields
select flex_value_set_name "Value Set",
descriptive_flexfield_name "Flexfield",
descriptive_flex_context_code "Structure",
end_user_column_name "Column"
from apps.fnd_descr_flex_column_usages a,
apps.fnd_flex_value_sets b
where a.flex_value_set_id = b.flex_value_set_id
and descriptive_flexfield_name not like '$SRS$%'
order by flex_value_set_name,
descriptive_flexfield_name,
descriptive_flex_context_code;
Value Set Usage by DescriptiveFlexfields
select d.flex_value_set_name "Value Set",
a.id_flex_name "Flexfield",
e.id_flex_structure_name "Structure",
c.segment_name "Segment"
from apps.fnd_id_flexsa,
apps.fnd_id_flex_structures b,
apps.fnd_id_flex_segments c,
apps.fnd_flex_value_sets d,
apps.fnd_id_flex_structures_tle
where a.application_id = b.application_id
and a.id_flex_code = b.id_flex_code
and b.application_id = c.application_id
and b.id_flex_code = c.id_flex_code
and b.id_flex_num = c.id_flex_num
and c.flex_value_set_id = d.flex_value_set_id
and c.application_id = e.application_id
and c.id_flex_code = e.id_flex_code
order by d.flex_value_set_name, a.id_flex_name,
e.id_flex_structure_name;
Read more: http://prasanthapps.blogspot.com/search/label/FLEX%20FIELDS#ixzz1pqIRIKgo
==========================
FAQ’s in SQL & PL/SQL
FAQ’s in SQL & PL/SQL
What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
What are the components of a PL/SQL Block?
Declarative part
Executable part
Exception part
What are the datatypes a available in PL/SQL?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are:
I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
What is difference between % ROWTYPE and TYPE RECORD?
% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.
Explain the two types of Cursors?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
What are the cursor attributes used in PL/SQL?
% ISOPEN – Used to check whether a cursor is open or not.
% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.
% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.
What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
What is the difference between implicit and explicit cursors?
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.
What are the different types of joins available in Oracle?
Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.
Self Join: If comparison comes in a single table
Cartesian Join: When tables are joined without giving any join condition.
Inner Join: The resultant set includes all the rows that satisfy the join condition.
Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition. The outer join operator Plus sign (+) will be included in the join condition.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
What is an autonomous transaction?
An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.
What is the difference between View and Materialized view?
Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.
What is dynamic SQL?
Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.
Can you use COMMIT in a trigger?
Yes but by defining an autonomous transaction.
What is the difference between anonymous blocks and stored procedures?
Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.
What is a package spec and package body? Why the separation?
Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.
Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.
What is Correlated Subquery?
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.
What is Sequence?
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
What is SQL Deadlock?
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
What is SQL*Loader?
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.
What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Explain forward declaration used in functions?
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.
Forward declarations are required in one specific situation: mutual recursion.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
What is the difference between Truncate and Delete Commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
What is the Purpose of HAVING Clause?
The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
What is INLINE View in SQL?
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
While creating a sequence, what does cache and nocache options mean?
With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
Does the view exist if the table is dropped from the database?
Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.
What is an Index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
What types of index data structures can you have?
An index helps to faster search values in tables. The three most commonly used index-types are:
B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
What is the difference between a “where” clause and a “having” clause?
“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.
Can a view be updated/inserted/deleted? If Yes – under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
What is tkprof and how is it used?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
What is explain plan and how is it used?
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
What are the Lock types?
Share Lock: It allows the other users for only reading not to insert or update or delete.
Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular object, others can only read.
Update Lock: Multiple user can read, update delete .
What is Pragma EXECPTION_INIT? Explain the usage?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
What is Raise_application_error?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.
What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.
What is the difference between Package, Procedure and Functions?
A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
Procedure is a sub program written to perform a set of actions and can return multiple values.
Function is a subprogram written to perform certain computations and return a single value.
Unlike subprograms packages cannot be called, passed parameters or nested.
How do you make a Function and Procedure as a Private?
Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.
What is Commit, Rollback and Save point?
Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.
Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
Rollback – This statement is used to undo work.
What is the difference between DDL, DML and DCL structures?
DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving. Ex; Commit, Rollback, Savepoint.
What is the difference between the snapshot and synonym?
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.
What is the difference between data types char and varchar?
Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement.
Tell some new features in PL/SQL in 10g?
-Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
-Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL
What is Overloading in PL/SQL?
Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.
What is a mutating and constraining table?
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
What is Nested Table?
A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows. In a sense, it’s a way of storing a one-to many relationship within one table.
What is Varying Array?
A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.
Give some most often used predefined exceptions?
a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)
Give some important Oracle supplied packages?
DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.
DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.
DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.
UTL_FILE: With this package, you can read from & write to Operating system files
UTL_HTTP: This package allows to make HTTP Requests directly from the database.
What is Instead Of Trigger?
This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.
What is the Sequence of Firing Database Triggers?
a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger
What is the Difference between PL/SQL Table & Nested Table?
PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.
What is the Difference between Nested Table & Varray?
Nested Tablea) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table
Varraya) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces
What are the various SQL Statements?
a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.
What is Rowid?
It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.
What is Partitioning?
It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning
What is a Cluster?
A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.
What is the Difference between Nested Subquery & Correlated Subquery?
Nested Subquerya) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query
Correlated Subquerya) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query
What is the Difference between Translate & Replace?
Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.
Read more: http://prasanthapps.blogspot.com/search/label/FAQ%E2%80%99s%20in%20SQL%20AND%20PL%2FSQL#ixzz1pqIXnGFr
================
GL Flow
GL Flow
GENERAL LEDGER
1. Creating Journal ( Batch, Header and Lines).
2. Creating Code Combination,
3. Creating Journal Source
4. Creating Journal Category
5. Understanding Accounting Period,
6. Understanding Key Flexfileds,
7. Understanding Descriptive Flexfields
8. Understanding SetUp of General Ledger
9. Understanding Account Inquiry form
10. Understanding GL Interface Program - Journal Import
11. Understanding Chart of Accounts.
12. Understanding Set of Books
Once you create a journal look for the data in below mentioned tables
Journal countains a Batch - GL_JE_BATCHES,
Header -GL_JE_HEADERS
Lines - GL_JE_LINES,
Once you create a Code Combination look for the data in GL_CODE_COMBINATIONS
Once you understand Account Inquiry Form look for the data in
GL_BALANCES
Once you create a Journal Source look for the data in
GL_JE_SOURCES
Once you create a Journal Category look for the data in GL_JE_CATEGORIES
Once you understand Accounting Period then check the data in
GL_PERIODS,
- GL_PERIOD_STATUSES,Once you understand Chart of Accounts structure then look for the data in,- GL_CHART_OF_ACCOUNTS
Once you understand Set of Books structure then look for the data in
- GL_SETS_OF_BOOKS
Once you understand GL Interface program then check tables
- GL_INTERFACE
- GL_IMPORT_REFERENCES
Go through the all the above tables again and again and be conversant about the joins.
Read more: http://prasanthapps.blogspot.com/search/label/GL%20Flow#ixzz1pqIbGgCO
===========
General Ledger Calendar FAQ [ID 134362.1]
General Ledger Calendar FAQ [ID 134362.1]
Applies to:
Oracle General Ledger - Version: 11.5 and later [Release: 11.5.0 and later ]
Oracle General Ledger - Version: 11.5 and later [Release: 11.5.0 and later]
Purpose
When investigating problems in this area the solution is often in the relevant White Paper which also provides a useful insight into the Topic. Please refer to the White Paper General Ledger Calendar Setup and Usage at Note:130539.1
Questions and Answers
1. You can change a period's specifications, except for the period type, as long as the period has not been used in a set of books.
You cannot change a calendar period that is open, closed, future enterable, or permanently closed in any set of books, or is included in an open budget or encumbrance year.
If it is difficult to use the periods as is, the best solution is to create a new set of books that uses a new calendar with the correct period dates. You can run Consolidation to move the General Ledger
balances from the old set of books to the new set of books. For step by step instructions on the Consolidation process, refer to the Oracle General Ledger User's Guides.
This may also mean that you need to re-install your subledgers so they will use the new set of books. To avoid this see Note:157642.1 How to Change the First Accounting Open Period
References: Note:76503.1 - Scripts to check calendar setup
2. Can I add an adjusting period to my calendar?
You can only define the number of periods that is specified by the Period Type you are using for your set of books. For example, if, for your set of books, you are using a Period Type of Month, and Month is defined to have 12 periods per year, you can only define 12 periods of Period Type Month for any one year, on the
Calendar form.
3. You defined a Period Type and now have decided that you want to change the periods per year that are associated with the Period Type. How can you do this?
Once you define a Period Type and save it, you cannot change it. If you need to change the number of periods then you have to define a new Period Type and a new Set of Books to use that new Period Type.
4. What are the year types 'Fiscal' and 'Calendar' used for ?
The Year Type (Fiscal or Calendar), is used only to determine which two digits to append to the system generated period name.
Regardless of which Year Type is used, the 'Year' entered on the Calendar form must be the same for all periods in your fiscal year - whether it is a calendar year or a fiscal year.
If Year Type = Calendar:
The last 2 digits of the 'From' date for the period are used.
If Year Type = Fiscal:
The last 2 digits of the value in the 'Year' are used.
References: Note.1013624.102 What are the year types 'Fiscal' and 'Calendar' used for?:
5. You are setting up a new set of books and calendar and not sure how much of the first year you need to define in the calendar. Do you need to define an entire year for the first year if you are only going to open the last period.
No, you do not have to define the entire year. You only define the periods you need, but of course without gaps between periods. Remember to define one prior period if you will be translating.
Example: You want to start entering transactions for January 2001, but you will be doing translations for January 2001 also, then you will need to define and open a period before that. In this example, December 2000. But it would be recommended to define all the periods so that the calender validation will not show any missing periods.
References: General Ledger User's Guide Note:1062487.6
6. Your calendar year is changing from fiscal year to calendar year and you already have a set of books that is using the fiscal year calendar. How can you change your calendar in GL?
There are 2 possible solutions. See Note 102460.1 for a full explanation.
Example:
- Your current fiscal year runs from April 1 through March 31.
- Your new year will run from January 1 through December 31.
- You will transition to the new year beginning on January 1 of the next year,
so you will have a short year from April 1 through December 31.
1) Create a new calendar and set of books
Create a new calendar, with the correct periods, and define a new set of books that uses that calendar. Run Consolidation to move your balances from the existing set of books to the new set of books.
You may also need to re-install your subledgers so they will use the new set of books.
OR
2) Update the existing calendar
a) Define the periods for your short year (April through December).
You must define the number of periods that are expected for the Period Type you are using. In this example, it is 12. So you will need to define 9 regular periods, and 3 adjusting periods. The Year
must be defined as the next sequential year.
b) Define the periods for the next year. This is the first year that runs from January through December. The Year must be defined as the next sequential year.
Note: This method depends on the subledgers taking their periods from General ledger so the periods must not already be defined. In the case of Fixed Assets, it would require major consultancy to change the year end period or YTD figures will always be inaccurate.
References: Note:102460.1 How to change GL Calendar from a Fiscal to a Calendar year?
7. You opened a period by mistake and want to set the status back to Future Enterable. How can you do this?
This is NOT supported. You can not set a period status back to future enterable after it has already been opened. If you do, it will cause corruption in the gl_balances table.
8. The year was specified incorrectly on your calendar periods. How can you fix this?
You cannot make changes to calendar periods in the form if the period is in an open budget year, open encumbrance year, or the status is Open, Future Enterable or Closed.
The solution is to create a new set of books that uses a new calendar with the correct period dates. You can run Consolidation to move the General Ledger data from the old set of books to the new set of books. For step by step instructions on the Consolidation process, refer to the Oracle General Ledger User's Guides.
This may also mean that you need to re-install your subledgers so they will use the new set of books.
9. The Year Type in your Period Types Form is not what you want. You used Fiscal instead of Calendar or visa versa.
The Year Type (Fiscal or Calendar), is used only to determine which two digits to append to the system generated period name.
References: Note:1013624.102 What are the year types 'Fiscal' and 'Calendar' used for?
10. Can you change a period name after the calendar is defined?
If the Calendar form lets you make the change, you can do it.
Otherwise, it means the period is in use, and you can not make the change.
11. What is the function of GL_DATE_PERIOD_MAP table and how is it populated?
The GL_DATE_PERIOD_MAP table stores the correlation between dates and non-adjusting periods for each accounting calendar and period type combination.
There is always a one-to-one correspondence between dates and non-adjusting periods since your accounting calendar must not have any gap or overlap between non-adjusting periods.
References
NOTE:1013624.102 - What are the year types 'Fiscal' and 'Calendar' used for?
NOTE:102460.1 - How to change GL Calendar from a Fiscal to a Calendar year?
NOTE:1062487.6 - Do you need to define entire calendar year if you will only use the last period?
NOTE:130539.1 - General Ledger Calendar Setup and Usage
NOTE:157642.1 - How to Change the First Accounting Open Period
NOTE:76503.1 - Scripts to Check the Setup of the General Ledger Calendar
Read more: http://prasanthapps.blogspot.com/search/label/General%20Ledger%20Calendar%20FAQ%20%5BID%20134362.1%5D#ixzz1pqIgfHVM
====================
Get On_Hand Quantities through API
Get On_Hand Quantities through API
This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve
You can also get the On-hand quantities from the table mtl_onhand_quantities
CREATE OR REPLACE function APPS.XX_TP_GET_ITEMS_TEST(v_organization_id in number,
v_inventory_item_id in number
)
return number
as
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2(1000);
--v_inventory_item_id VARCHAR2(250) := '64';
--v_organization_id VARCHAR2(10) := '12';
Cursor c_item_info is
select msb.INVENTORY_ITEM_ID,msb.ORGANIZATION_ID
from
MTL_SYSTEM_ITEMS_B msb
where
msb.ORGANIZATION_ID=nvl(v_organization_id,msb.ORGANIZATION_ID)
and msb.INVENTORY_ITEM_ID=nvl(v_inventory_item_id,msb.INVENTORY_ITEM_ID);
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);
For i in c_item_info
LOOP
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => i.ORGANIZATION_ID,
p_inventory_item_id => i.INVENTORY_ITEM_ID,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => APPS.INV_QUANTITY_TREE_PVT.g_all_subs, --3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr);
DBMS_OUTPUT.put_line ('on hand Quantity :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);
return v_qty_oh;
end loop;
exception
when others then
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Read more: http://prasanthapps.blogspot.com/search/label/Get%20On_Hand%20Quantities%20through%20API#ixzz1pqIkPMhy
=================
Get the Cost of an Item (API)
API to Get the Cost of an Item
Group Team
Below code can be used to get the cost of an Item.
The function returns the item cost for a given item and the organization.
All the parameter used in the Api are IN Parameter. Cost_group_id and Cost_type_id have the default value as NULL.
We can pass cost_group_id or cost_type_id, if you desire most specific cost Information. Do not pass both the cost group and Cost type at the same time.
API can fail for the below scenario
Invalid item/organization combination.
Item is not cost enabled.
Item has no cost in the specified cost group or cost type.
DECLARE
v_inventory_item_id NUMBER := 832215;
v_organization_id NUMBER := 209;
v_api_version NUMBER := 1.1;
v_cost_type_id NUMBER;
v_item_cost NUMBER := 0;
v_cost_group_id NUMBER;
BEGIN
dbms_output.put_line('API CST_COST_API to get the Item Cost);
v_item_cost := CST_COST_API.get_item_cost
(p_api_version => v_api_version,
p_inventory_item_id => v_inventory_item_id,
p_organization_id => v_organization_id,
p_cost_group_id => v_cost_group_id,
p_cost_type_id => v_cost_type_id
);
IF v_item_cost is NULL THEN
dbms_output.put_line('Issue in getting the Item Cost and Issue can be any one specified in the error scenario of the post');
ELSE
dbms_output.put_line('Item Cost is ' || v_item_cost);
END IF;
EXCEPTION
WHEN
OTHERS
THEN
dbms_output.put_line('Error in calling api and the error is ' ||
substr(SQLERRM,
1,
200));
END;
Read more: http://prasanthapps.blogspot.com/search/label/Get%20the%20Cost%20of%20an%20Item%20%28API%29#ixzz1pqIoBlkR
==============
HOW TABLES ARE EFFECTED
GL Lession
Maintenance and Relevant ReportsCalendar Maintenance:
GL Can't be started without defining the first open period from the Open/Close Calendar option. One has to be very careful when selecting the first period as once opened a period prior to that can't be opened. Once opened, on an ongoing basis periods have to be opened and closed. New Years whould be required to be appended to the existing calendars.
Account Values Maintenance:
On a regular basis lot of values would have to be opened, disabled etc.
Use Mass Maintenance to move balances by period from one account to another or merge balances by period from multiple accounts into a single account. The moved/merged balances are added to the existing balances in your target accounts. To change a move/merge, one can reverse it and restore account balances to their previous amounts.
During a move/merge operation the financial integrity between GL and its sub ledgers is maintained.
Document Sequencing:Every time the validity of the Document sequence is over, define and assign new sequence.
Currency Rate Maintenance
Standard Reports:
Account Analysis, Trial Balance (Detailed, Summary, Expanded), Budget Reports, Chart of Accounts Reports and
Listings, Currency Listings Reports, FSG Reports on Row/Column Set Details, GL Report, Journal Reports and
Execution Reports.
Closing Procedure:
Set the status of the first accounting period in the new fiscal year to Future Entry.
If the business rules require reversing entries at the beginning of every period, generate and post accruals from the prior period. If prior period reversals were not generated and posted at the beginning of this period, then generate reversals
Transfer data from all of sub ledgers and feeder systems to the GL_INTERFACE table. Review and Post the imported journal entries. Close the period for each sub ledger. This prevents future sub ledger transactions from being posted to GL in the same period. Perform reconciliations of subsidiary ledgers by reviewing and correcting balances.
Generate all recurring journals and step–down allocations.
Revalue balances to update foreign currency journals to functional currency equivalents.
Post all journal entries, including: manual, recurring, step–down allocations, and reversals.
Update any unpostable journal entries and then post them again.
Run GL reports, such as the Trial Balance reports, Account Analysis reports, and Journal reports.
Translate balances to any defined currency if report in foreign currencies is required.
Consolidate subsidiary SOBs in case of multiple companies.
If using a calendar with an adjusting period that represent the last day of the fiscal year, close the current period and open the adjusting period. Create and post adjusting entries and accruals in the adjusting period.
Run Trial Balance reports and other GL Reports in the adjusting period after adjustments are made.
If it is required to have an actual closing journal entry that shows the closing of income statement accounts to retained earnings, submit the Create Income Statements Closing Journals program. This program creates an auditable closing journal entry. The income statement will reflect zero balances on posting
If local accounting rules require balance sheet to be closed, submit the Create Balance Sheet Closing Journals program. Balance sheet will now reflect zero balances on posting.
Close the last period of the fiscal year and Open the first period of the new fiscal year to launch a concurrent process to update account balances. Opening the first period of a new year
automatically closes income statement and posts the difference to retained earnings account specified in the SOB form.
Perform Year–End Encumbrance Procedures (if applicable).
Run FSG reports for the last period of the year.
If balance sheet is closed at year–end, reverse the Balance Sheet Closing Journals to repopulate balances of balance sheet accounts for the new year.
Read more: http://prasanthapps.blogspot.com/search/label/HOW%20TABLES%20ARE%20EFFECTED#ixzz1pqIsUunr
============
GL Tables, AP Tables,HZ TABLES, AR TABLES AND FLOW DIAGRAM,FND TABLES
GL Tables
General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each category.
Ledgers Tables:
GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information.
GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application.
Period Tables:
GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar form.
GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.
GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the period type name, the number of periods per fiscal year, and other information.
Journal Tables:
GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information.
GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry.
GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line.
GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. This table corresponds to the Journal Sources form.
GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.
Conversion and consolidation tables:
GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a mapping’s ID, name, description, and other information. This table corresponds to the first window of the Consolidation Mappings form. You need one row for each consolidation mapping you define.
GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of the Transfer Consolidation Data form.
GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type.
GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in sets of books with average balances enabled.
Budgeting tables:
GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one budget type, ‘STANDARD’. Therefore, this table always contains only one row.
GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget Organization form.
GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this table, and then deletes the rows in this table that it used.
Interface Tables:
GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches.
GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. When Journal Import completes, it deletes these rows from the table.
GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget amounts for an account.
PAYABLES TABLES
AP_SUPPLIERS:
This table replaces the old PO_VENDORS table.
It stores information about your supplier level attributes.
Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers.
The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.
AP_SUPPLIER_SITES_ALL:
This table replaces the old PO_VENDOR_SITES_ALL table.
It stores information about your supplier site level attributes.
There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
Each row includes the supplier reference, purchasing, invoice, and general information.
AP_INVOICES_ALL:
It contains records for invoices you enter.
There is one row for each invoice you enter.
An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.
AP_INVOICE_LINES_ALL:
It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
An invoice can have one or more invoice lines.
An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.
AP_INVOICE_DISTRIBUTIONS_ALL:
It holds the distribution information that is manually entered or system-generated.
There is one row for each invoice distribution and a distribution must be associated with an invoice.
An invoice can have multiple distributions.
AP_INVOICE_PAYMENTS_ALL:
It contains records of invoice payments that you made to suppliers.
There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.
AP_PAYMENT_SCHEDULES_ALL:
This table stores information about scheduled payment information on invoices.
AP_PAYMENT_HISTORY_ALL:
It stores the clearing/unclearing history for payments.
It also stores the maturity history for future dated payments.
The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.
AP_BATCHES_ALL:
It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
There is one row for each batch of invoices you enter.
If you enable Batch Control, each invoice must correspond to a record in this table.
Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.
AP_CHECKS_ALL:
It stores information about payments issued to suppliers or refunds received from suppliers.
There is one row for each payment you issue to a supplier or refund received from a supplier.
Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.
AP_HOLDS_ALL:
It contains information about holds that you or your Oracle Payables application place on an invoice.
For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
An invoice may have one or more corresponding rows in this table.
Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.
AP_BANK_ACCOUNTS_ALL:
It contains information about your bank accounts.
There is one row for each bank account you define and each bank account must be affiliated with one bank branch.
AP_BANK_ACCOUNT_USES_ALL:
It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.
AP_CARDS_ALL:
It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.
AP_TRIAL_BALANCE:
It contains denormalized information about invoices and payments posted to the accrual set of books.
HZ(TCA) tables in Oracle Receivables
This article describes few important HZ tables in AR and their relationships with each other.
HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.
Few Important Columns are
PARTY_ID: Party identifier
PARTY_NUMBER: Unique identification number for this party
PARTY_NAME: Name of the party
PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.
HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are
PARTY_SITE_ID: Party site identifier.
PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
PARTY_SITE_NUMBER: Party site number.
PARTY_SITE_NAME: User-defined name for the site.
ADDRESSEE: Addressee information.
HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.
Few Important Columns are
LOCATION_ID: Unique identifier for this location
COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
ADDRESS1: First line for address
ADDRESS2: Second line for address
ADDRESS3: Third line for address
ADDRESS4: Fourth line for address
CITY: City
POSTAL_CODE: Postal Code
STATE: State
ADDRESS_KEY: Derived key that facilitates fuzzy searches
HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.
Few Important Columns are
CUST_ACCOUNT_ID: Customer account identifier
PARTY_ID: A foreign key to the HZ_PARTY table.
ACCOUNT_NUMBER: Account Number
CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
CUSTOMER_CLASS_CODE: Customer class identifier
HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.
Few Important Columns are
CUST_ACCT_SITE_ID: Customer site identifier
CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
BILL_TO_FLAG: Indicates if this is a Bill-To site.
SHIP_TO_FLAG: Indicates if this is a Ship-To site.
MARKET_FLAG: Indicates if this is a Marketing site.
HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.
Few Important Columns are
SITE_USE_ID: Site use identifier
CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.
HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are
CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
STATUS: Indicates whether the profile is active or inactive
HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.
HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.
AR Tables:A Diagrammatic Relation
Key FND Tables in Oracle Application
Here there are few key FND tables that we use in our AOL queries.
FND_APPLICATION:
Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL:
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS:
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES:
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS:
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS:
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL:
Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES:
Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE:
Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS:
Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS:
Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS:
This table stores output files created by Concurrent Request.
FND_CURRENCIES:
Stores information about currencies.
FND_DATABASES:
It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES:
Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS:
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL:
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS:
Stores language-independent information about a document.
FND_EXECUTABLES:
Stores information about concurrent program executables.
FND_FLEX_VALUES:
Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS:
Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES:
Stores information regarding languages and dialects.
FND_MENUS:
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL:
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES:
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS:
Stores information about user profile options.
FND_REQUEST_GROUPS:
Stores information about report security groups.
FND_REQUEST_SETS:
Stores information about report sets.
FND_RESPONSIBILITY:
Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL:
Stores translated information about responsibilities.
FND_RESP_FUNCTIONS:
Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS:
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES:
Stores information about the registered sequences in your applications.
FND_TABLES:
Stores information about the registered tables in your applications.
FND_TERRITORIES:
Stores information for countries, alternatively known as territories.
FND_USER:
Stores information about application users.
FND_VIEWS:
Stores information about the registered views in your applications.
Read more: http://prasanthapps.blogspot.com/search/label/HZ%20TABLES#ixzz1pqIyg6yH
==============
Hard Reservation for Standard Sales Order line against Purchase Order
Hard Reservation for Standard Sales Order line against Purchase Order:-
-------------------------------------------------------------------------------------
Note:- You need to have the ASCP Module Installed for the following script to work.
Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.
When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.
Here, we are considering if ASCP has decided to create the Purchase Order.
For that you can use the following script.
/************************************************************
Hard Reservation for Standard Sales Order line against Purchase Order
Created by : PRASANTH
Creation Date :
Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.
For others, the input values can be passed directly without CURSOR.
************************************************************/
PROCEDURE create_po_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)
IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;
CURSOR C_PO_RESV(c_line_id) ----- Cursor to select Purchas Orders pegged to the standard sales order line
IS
SELECT
DISTINCT pla.PO_HEADER_ID,
pda.PO_DISTRIBUTION_ID,
mfp.ALLOCATED_QUANTITY
FROM PO_LINES_ALL pla,
PO_HEADERS_ALL pha,
PO_DISTRIBUTIONS_ALL pda,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND pla.PO_HEADER_ID=pha.PO_HEADER_ID
AND pla.PO_HEADER_ID=pda.PO_HEADER_ID
AND pla.PO_LINE_ID = pda.PO_LINE_ID
AND pla.PO_HEADER_ID=ms.DISPOSITION_ID
AND pla.PO_LINE_ID= ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;
BEGIN
--- initialize required input params to call reservation api--
--- get the item id--
SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);
--- get the sales_order_id from mtl_sales_orders
--- table which will be passed as one of the input params-
SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);
FOR C_REC IN C_PO_RESV(p_line_id)
-------------- sending the sales order line to the cursor variable
LOOP
----- Initialize al required inputs to perform HARD RESERVATION------
--fnd_global.APPS_Initialize(2572700,20559,300);
--p_user_id, p_resp_id, p_resp_appl_id
p_rsv.reservation_id := NULL; -- cannot know
------------will be generated once reservation is successful
p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id;
---------------------------mtl_parameters.organization id
p_rsv.inventory_item_id := l_inventory_item_id;
---------------------------mtl_system_items.Inventory_item_id
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;-------'SALES ORDER';
p_rsv.demand_source_header_id :=l_source_header_id;
--------------------mtl_sales_orders.sales_order_id for order number
p_rsv.demand_source_line_id := p_line_id;
-------------------- oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_po;
p_rsv.supply_source_header_id :=C_REC.PO_HEADER_ID;
------------------------Header id of PO
p_rsv.supply_source_line_id :=C_REC.PO_DISTRIBUTION_ID;
------------------------Distribution id of PO
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;
-------------subinventory code can be mentioned
p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;--17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;--'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;
------------------------------ CASE II-------------------------------------------
--- Validating whether the pegging is done WITH AN EXTERNAL PURCHASE ORDER---
---- FInally performing Hard Reservation ---------
inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);
IF x_status='S' THEN --- HARD RESERVATION IS DONE SUCCESFULLY ---
COMMIT;
dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);
ELSE
if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;
--- HARD RESERVATION API fails ---
dbms_output.put_line('Reservation API Error Message: '||l_error_message);
END IF;
EXCEPTION
WHEN OTHERS THEN ------------------ In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);
END create_po_hard_reservation;
Read more: http://prasanthapps.blogspot.com/search/label/Hard%20Reservation%20for%20Standard%20Sales%20Order%20line%20against%20Purchase%20Order#ixzz1pqJ4KC8r
===============
Customer TCA Architecture And API
Customer TCA Architecture And API
Overview:Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables.
Architecture
Customer_TCA_Architecture_API
Create Organization
DECLARE
p_organization_rec hz_party_v2pub.organization_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
BEGIN
p_organization_rec.organization_name := 'erpschools';
p_organization_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_v2pub.create_organization ('T',
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id
);
DBMS_OUTPUT.put_line ('party id ' || x_party_id);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables.
Create a Location
DECLARE
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_location_rec.country := 'US';
p_location_rec.address1 := '2500 W Higgins Rd';
p_location_rec.address2 := 'Suite 920';
p_location_rec.city := 'Thumuluru';
p_location_rec.postal_code := '60118';
p_location_rec.state := 'IL';
p_location_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_location_v2pub.create_location(
'T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('location id '||x_location_id);
dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END
Note: The above API shall create an address record in hz_locations table.
Create a Party Site:
Use the organization_id and location_id created above and create a party site.
DECLARE
p_party_site_rec hz_party_site_v2pub.party_site_rec_type;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2 (2000);
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_party_site_rec.party_id := 1272023;
p_party_site_rec.location_id := 359086;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line ('party site id ' || x_party_site_id);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_party_sites table.
Create Party Site Use
Use the above party site created
DECLARE
p_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
x_party_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_party_site_use_rec.site_use_type := 'SHIP_TO';
p_party_site_use_rec.party_site_id := 349327;
p_party_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_party_site_v2pub.create_party_site_use ('T',
p_party_site_use_rec,
x_party_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create a Contact Point
DECLARE
p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
p_edi_rec hz_contact_point_v2pub.edi_rec_type;
p_email_rec hz_contact_point_v2pub.email_rec_type;
p_phone_rec hz_contact_point_v2pub.phone_rec_type;
p_telex_rec hz_contact_point_v2pub.telex_rec_type;
p_web_rec hz_contact_point_v2pub.web_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_contact_point_id NUMBER;
BEGIN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := '1272023';
p_contact_point_rec.primary_flag := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_phone_rec.phone_area_code := '650';
p_phone_rec.phone_country_code := '1';
p_phone_rec.phone_number := '506-7000';
p_phone_rec.phone_line_type := 'GEN';
p_contact_point_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_contact_point_v2pub.create_contact_point ('T',
p_contact_point_rec,
p_edi_rec,
p_email_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create an Org Contact:
DECLARE
p_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_org_contact_rec.department_code := 'ACCOUNTING';
p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
p_org_contact_rec.decision_maker_flag := 'Y';
p_org_contact_rec.job_title_code := 'APC';
p_org_contact_rec.created_by_module := 'ERPSCHOOLS_DEMO';
p_org_contact_rec.party_rel_rec.subject_id := 16077;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 1272023;
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
x_org_contact_id,
x_party_rel_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as 'PARTY_RELATIONSHIP'.
Create a Customer Account:
DECLARE
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_person_rec hz_party_v2pub.person_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profilerec_type;
x_cust_account_id NUMBER;
x_account_number VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_cust_account_rec.account_name := 'John''s A/c';
p_cust_account_rec.created_by_module := 'ERPSCHOOLS_DEMO';
p_person_rec.person_first_name := 'John';
p_person_rec.person_last_name := 'Smith';
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F',
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Note:
This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.
Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.
Create a Customer Account Site
Use an existing Party Site
DECLARE
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_cust_acct_site_id NUMBER;
BEGIN
p_cust_acct_site_rec.cust_account_id := 3472;
p_cust_acct_site_rec.party_site_id := 1024;
p_cust_acct_site_rec.LANGUAGE := 'US';
p_cust_acct_site_rec.created_by_module := 'TCA-EXAMPLE';
hz_cust_account_site_v2pub.create_cust_acct_site ('T',
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
Create Customer Account Site Use Code:
DECLARE
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
p_cust_site_use_rec.cust_acct_site_id := 3580;
p_cust_site_use_rec.site_use_code := 'INV';
p_cust_site_use_rec.LOCATION := 'TCA';
p_cust_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO';
hz_cust_account_site_v2pub.create_cust_site_use ('T',
p_cust_site_use_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data
);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;
More Customer API's:
Org Contact Role
Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile
HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount
HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating
HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person
JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories
JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts
HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
Read more: http://prasanthapps.blogspot.com/search/label/Customer%20TCA%20Architecture%20And%20API#ixzz1pqJILoR3
===============
Customer Tables Understanding
Customer Tables Understanding
Hello everyone,
This is a just how customer informations are stored in apps tables. This post gives you clear explanation of how customers information are getting stored.
Customer Created Name: TESTCUST_1
Customer Accounts Created under the customer TESTCUST_1:
1.TESTCUST_1_1
2.TESTCUST_1_2
For Customer:
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTCUST_1'
Party Id: 307957
For Customer Accounts:
SELECT * FROM HZ_CUST_ACCOUNTS hca
WHERE hca.ACCOUNT_NAME IN ('TESTCUST_1_1','TESTCUST_1_2')
Account Number: 5391,5392
Account Id: 96675,96677
Customer Accounts Sites:
Under the Customer Account:
1.TESTCUST_1_1
Sites Name:
1.TESTCUSTS1_1
2.TESTCUSTS1_2
/*2.TESTCUST_1_2
Site Name:
1.TESTCUSTS2_1
2.TESTCUSTS2_1*/
SELECT * FROM HZ_CUST_ACCT_SITES_ALL hcsa
where hcsa.CUST_ACCOUNT_ID IN (96675) -- Customer Account Id of TESTCUST_1_1 ---Sites - TESTCUSTS1_1,TESTCUSTS1_2
Customer Account Site Id: 10554 - TESTCUSTS_1_2
10558 - TESTCUSTS_1_1
SELECT * FROM HZ_PARTY_SITES
WHERE PARTY_ID = 307957 -- Check Party Site Name : TESTCUSTS1_1,TESTCUSTS1_2
Party site Id: 179402 - TESTCUSTS_1_2
179404 - TESTCUSTS_1_1
For the Site TESTCUSTS1_1:
Contact Creation:
1.Contact Name: TESTS_1_1
2.Telephone - created
3.Email
There are two Id need to get, one is subject id and other is object id
1.Subject Id is related to Organization that is customer Party id here the party id is
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTCUST_1'
Party Id: 307957 - subject Id
2.Object Id is related to the contact name created that is Party Id of the person(Contact)
SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'TESTS_1_1'
Party Id : 307959 - Object id
Now Check in the Relationship table using subject id and object id
SELECT * FROM HZ_RELATIONSHIPS hr
WHERE hr.SUBJECT_ID = 307957
and hr.OBJECT_ID = 307959
and the reverse entry will also be there
SELECT * FROM HZ_RELATIONSHIPS hr
WHERE hr.SUBJECT_ID = 307959
and hr.OBJECT_ID = 307957
Relatonship Id:137025
Party Id:307960
SELECT * FROM HZ_PARTIES hp
WHERE hp.PARTY_ID = 307960 -- Party_type will be PARTY_RELATIONSHIP
SELECT * FROM HZ_ORG_CONTACTS hoc
where hoc.PARTY_RELATIONSHIP_ID = 137025 -- Relatioship Id
Org Contact Id: 118071
SELECT * FROM HZ_CONTACT_POINTS hp --HZ_CUST_CONTACT_POINTS hcp
WHERE hp.OWNER_TABLE_ID = 179404 --Party Site Id of the Contact - TESTCUSTS_1_1
SELECT * FROM HZ_CUST_ACCOUNT_ROLES hcr
WHERE hcr.CUST_ACCT_SITE_ID = 10558 --Customer Account Role created when creating contacts --This is the Customer Site Id of TESTCUSTS_1_1
===========
No comments:
Post a Comment