Inventory -> More queries

http://www.oracleport.com/oracle-r11-r12-important-queries/

Oracle R11, R12 Important Queries (Concurrent , Inventory, Purchaing , HR, Payrol , OPM)

To Find Duplicate Item Category Code

SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition

SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management

SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varry)

SELECT ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varry)

SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup
select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.
select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to Find Responsibilities assigned to particular user.

SELECT b.responsibility_name NAME
FROM apps.fnd_user_resp_groups a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND u.user_id = (select user_id from FND_USER where user_name=:User_name)
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL

Query To find the Request Run By Concurrent

select b.user_concurrent_queue_name, c.request_id
from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.concurrent_queue_id = b.concurrent_queue_id
and a.concurrent_process_id = c.controlling_manager;

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliverselect sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query(select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item,

Query to Find out On Hand Quantity of specific Item Oracle inventory
(select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id) onhand,
– Qty Issued by X No clue what you want here
–Qty On Order,Expected deivery date(select sum(ordered_quantity),scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info,
–Toatl Received Qty
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0) tot_rec_qty,
–Total received Qty in 9 months
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_rec_qty_9mths,
–Total issued quantity in 9 months(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) avg_month_consumption; Find detail of specific Applications

Running in System System Administrator

SELECT application_id,APPLICATION_NAME
FROM fnd_application_tl
WHERE application_name = ‘Purchasing’
AND rownum = 1;

Find the Request Groups and Concurrent Progams

SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
–and c.USER_CONCURRENT_PROGRAM_NAME like ‘%Purchase Order%’
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id;

Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)

Oracle Purchasing

1.Supplier Mgmt --> TCA/AP
2. RFQs/Quatations.
3. Catalog Content Management.
4. Requisitions.
5. PO
6. Receiving
7. Invoice/Payment
8. Reporting
9. Supplier Performance

Purchasing tech Stack

--> XML gateway
--> e-Commerce gateway
-->Alert
--> Workflow
--> XML publisher

Procurement Suite

1. iSupplier --> after awarding
2. iprocurement --> enter[rcie
3. Serice procurement
4. procurement contracts
5. DBI for procurement
6. Sourcing --> before awarding
7. Purchasing intelligence


Request --> requirement --> selection --> requision --> approval
Buy --> requisition --> source --> negotiate --> contracct
Supply --> confirm process ship invoice
remit -->  receive deliver match pay


P2P --> Direct material/Indirect material


Oracle p@p --> Demand (Req) ---> RFQ --> Source(Quotation) --> order(PO) --> Receive --> pay

Account Payable R12 Delta features

Account parables --> we have to setup supplier bank branches and their accounts  to pay money to them.

Now in R12 we can first define

1. bank
2. Then Branch of back and
last the Bank Account.

we also have something called supplier merge.

We can define various payment methods for suppliers.


There are some new tables introduced in Payable namely

 1. AP_Suppliers
 2.  ap_supplier_sites_all
3. ap_supplier_contacts
4. ap_supplier_int_rejections.

New views for backword compatibility

1. PO_Vendors
2. PO_vendor_sites_all
3. PO_vendor_contacts

 Changed Program

1. Supplier Open Interface import
2. Supplier sites open interface
3. Supplier site contacts open interface

New APIs

AP_vendor_pub_pkg.create_vendor
ap_vendor_pub_pkg.update_vendor
ap_vendor_pub_pkg.create_vendor_site
ap_vendor_pub_pkg.update_vendor_site
ap_vendor_pub_pkg.create_vendor_contact


defining banks

1. use bank window to enter bank information.
2. AR and AP share bank defn.
2. Bank --> multi branch --> multi account --> one account --> multiple associations.

AP --> Internal bank accounts and supplier bank accounts

Internal Bank account --> define payment document --> payment formats associated with them to specify a payment method.

check
electic
wire
cleaning

=============================

MOAC

--> same old shit multiple operating units access from single resp.--> BIG DEAL

Three Model

1. Decentralized Model --> each OU take care of itself
2. 

AR : Adjustment 2

http://www.oracleappshub.com/accounts-receivable/ar-adjustments-detailssetup-flow-accounting-and-api/


To Read Later :-> http://asoracle.blogspot.sg/2007/11/accounting-for-oracle-receivables.html  
                                http://www.cathycakebread.com/papers/_setup.htm

How AR adjustment works

Lets try to understand how adjustment works in Oracle.

While Creation

When you create an adjustment activity an accounting segments is required.This is GL account that will be used as the offset account. The other account used will be the Receivable REC account of the transaction being adjusted.As mention above, you are allowed to adjust Invoices, Debit Memos and Credit Memos.

AR_ADJUSTMENTS_ALL table contains code_combination_id.

This is the code_combination_id of the Receivable account of the Invoice,Debit Memo, or Credit Memo the adjustment is against.

The table contains the receivables_trx_id which identifies as adjustment transaction type.

When you create an adjustment the account that exists on the adjustment transaction type, at the moment the transaction is created, is the one that will be used as the offset account.

Adjustments can be either negative or positive, and there should be always one and only row created on the ar_adjustments_all table for each adjustment. On posted to GL this one row is used to create two transactions passed to GL_INTERFACE table.

During Approvals

Approval required for adjustment , as part of pre-requsite setup.

You can create your own approval statuses, and they work as if they were interim approval steps.

The only approval that is recognized is the final one, which results in an 'A' in the status field on this table.

You must assign approval limits to users by currency.The limit should be in the range of negative adjustments amount to positive adjustments amount on AR_ADJUSTMENTS_ALL table, there is a field named 'postable'. When you set the status of an adjustment to 'A' for approved, this field should automatically be set to 'Y' for postable.

Once an adjustment is approved, it can be selected for posting to General Ledger.

This would be selected in a normal AR Gl transfer program posting run, providing it met the qualifications of being approved, with a GL_date within the posting range, and had not been posted before (posting_control_id = -3).

During posting, the Receivable and offset transactions are created and passed to General Ledger table GL_INTERFACE.

On apply with a transaction

When you create an adjustment against a transaction, the customer_trx_id and payment_schedule_id on AR_ADJUSTMENTS_ALL associates the adjustment with the transaction.

If an adjustment is approved with a status of 'R' for rejected, No impact on invoice balance and cann't proceed for GL posting .

If an adjustment has not been approved with a status of 'A', it is a pending adjustment. The total of all pending adjustments is stored in the amount_adjusted_pending on AR_PAYMENT_SCHEDULES_ALL, and is not reflected in a transaction balance.

If an adjustment has been approved with a status of 'A', it is reflected in the amount_adjusted and the balance on AR_PAYMENT_SCHEDULES_ALL.

 Ways to Create Adjustment in Oracle

Creating Adjustment in Oracle can be performed by 3 options

1. Adjustment via Transaction Window
2. Adjustment via Receipt Application Window
3. Adjustment via API's Call

 Option 1

Follow the path above to display the Account Details window. A Find window will appear on top of the Account Details window. This will allow you to search for transactions based on a number of criteria (including Transaction Number, Customer Name, and Amounts):

Navigation: Transactions > Transactions > Click Toolbar Find

Once you have entered your search criteria click Find. Records matching you search will be displayed in the Account Details window:

Select the invoice you wish to do adjustment (by clicking on the line) and click the Adjust button. This will display the Adjustments screen:

Click in the Activity Name field and open the list of values and choose any one of them and then save the record.

 Options 2 : Adjustment via Receipt Application Window

You can create customer Receipt and after finishing the Application of Invoice then click (B) Adjustments and system will show same Adjustment windows and rest is similar to option 1.

 Options 3 : By API Call

Public API Exist as third options which allows users to create, approve, update, and reverse adjustments for invoices using simple calls to PL/SQL functions.

The Adjustment API has the following basic functionality via different API calls:

1. Creating an adjustment (R11i & R12)
2. Modifying an adjustment (R11i & R12)
3. Approving an adjustment (R11i & R12)
4. Reversing an adjustment (R11i & R12)
5. Create line level adjustment (This is only applicable in case of R12)

We will take details of API's in Next Post.

AR : Adjustments 1

http://docs.oracle.com/cd/A60725_05/html/comnls/us/ar/recpts10.htm


Lookup --> select * from ar_lookups where lookup_type = 'ADJUSTMENT_TYPE';

LOOKUP_CODE    MEANING

CHARGES    Charges Adjustments
FREIGHT    Freight Adjustments
INVOICE    Invoice Adjustments
LINE    Line Adjustments
TAX    Tax Adjustments

===================================================================================
Approval Type

select LOOKUP_CODE , MEANING from ar_lookups where lookup_type = 'APPROVAL_TYPE';


LOOKUP_CODE    MEANING

A    Approved
M    More Research
R    Rejected
U    Unaccrued Charges
W    Waiting Approval

==========================================================================
select LOOKUP_CODE , MEANING from ar_lookups where lookup_type = 'INV/CM';

LOOKUP_CODE    MEANING

BR    Bills Receivable
CB    Chargeback
CM    Credit Memo
DEP    Deposit
DM    Debit Memo
GUAR    Guarantee
INV    Invoice
PMT    Payment
=======================================================================
User Guides can be found on following links.

http://www.vpaf.uni.edu/obo/accts_receivable/documents/ar_reportss.pdf

======================================================================

Oracle Adjustments

http://www.oracleappshub.com/accounts-receivable/ar-adjustments-detailssetup-flow-accounting-and-api/

An Adjustment in AR is an amount added or subtracted from the amount due of an Invoice, Debit Memo, Chargeback, Deposit, or Guarantee.

Oracle AR lets you create Automatic or Manual Adjustments.

Receivables lets you make either Positive or Negative Adjustments to your Invoices, Debit Memos, Chargebacks, On–account Credits, Deposits, and Guarantees.

You can approve Adjustments that are within your approval limits and give pending statuses to Adjustments that are outside your approval limits.

You can also automatically write off debit items that meet your selection criteria.

 Business need for Adjustment

Its very similar to write-off. If there is any unapplied balance in invoice after application of receipt in small value for rounding off purpose , that will be adjusted while making receipt application.

Or sometime, when Invoice amount is wrongly entered you can also add a line by adding or reducing amount.

These are very common across all industry.Telecom, Health care and retail sectors the need is very high for such functionality.

Prerequisites

To enable this Functionality you need to perform these step-up

• You need to define adjustment activity
• Navigation :-> AR Super User :->Setup --> receipts --> receivable activities.

• You need to define approval limits

Defining the Approval limits for Adjustments

Setup > Transactions > Approval Limits

• You need to define adjustment reason lookups

 Irrelevant