,

What Actions are supported in import standard purchase order ?

What Actions are supported?

ORIGINAL – create a new document
ADD – add new lines onto an existing document
UPDATE – update existing line information for a document
REPLACE – Replace the existing document
Which interface tables are involved?

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)

/by
, , , , ,

Oracle Purchase Order – Functional Flow

Functional Training  of Purchase Order Flow
What is flow of PO:

Step 1:We will be raising the Requisitions for the items which are needed.
Requisition is 2 types • Internal Requisition • Purchase
Internal Requisition is used when we are getting the items from one organization to another organization of the same business group.
Purchase Requisition is raised when we are getting the items from outside the business group.

Step 2:Once the Requisition has been approved, we will be sending (Request For Quotes) RFQ’s to different Suppliers. It contains the information regarding the type of RFQ, terms and conditions, shipments, currency.
RFQ is of 3 types
• Bid RFQ • Catalog • Standard
BID: Used for a fixed specific quantity, Location and date. This will be used for large or expensive price of equipments. That you have never order before. There won’t be price any breaks for a BID Quotation.
CATALOG: This will be used for high volume items for which your supplier sends information regularly. The CATALOG RFQ includes price breaks at different quantity levels.
STANDARD: It is used for items we need only once or not very often for a specific fixed quantity, location and date. It includes price breaks at different quantity levels.

Step 3:We will be receiving the quotations from different suppliers. Quotation is of 3 Types.
• Bid
• Catalog
• Standard

Step 4:Based on the quotations, we will be deciding the supplier and purchasing order is given to that supplier.
Purchasing is of 4 types
• Standard
• Planned
• Blanket
• Contract

Step 5:Once we receive the items from the Supplier we will issue the receipts to the Supplier. This is done in 3 ways.
• Two-Way: In 2-way we will compare PO and Invoice.• Three-Way: In 3-way we will compare the PO, Receipt and Invoice.• Four-Way: In 4-way we will compare PO, Receipt, Inspection and Invoice.

2-Way Matching verifies that Purchase order and invoice information match within your tolerances as follows:Quantity Billed <-Quantity Ordered Invoice Price <- Purchase Order Price (<- Sign is used because of tolerances)

3-Way Matching verifies that the receipt and invoice information match with the quantity tolerances defined:Quantity billed Quantity received

4-Way Matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:Quantity billed <- Quantity accepted. (Acceptance is done at the time of Inspecting goods).Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character).

RECEIPT REQUIRED INSPECTION REQUIRED MATCHING
Yes Yes 4-Way
Yes No 3-Way
No No 2-Way
In ‘More’ alternative region of Shipments block, you define the Invoice Matching option, where you can choose 2-way, 3-way or 4-way match.

Step 6:Because of these Transactions Inventory and Payables get affected.

/by
, , ,

Purchasing Interview Questions

Q. AME is possible in PO approval ??
NO. AME for Req approval
Q. Can we create Position heiracchy for Requisition Approval
Yes
Q. vacation rule has to be created by the same user…if being as a first person in the approver  goup the user not able to create vacation rule and goes on leave…then any other way, by  which we can handle that out of scope
Q. Can I get the recording version of yesterda’s session?
No.
Q. Where did we do setups for taxes
Ebiz Tax
Q. Is AME diff in 11i and R12
Conceptually, AME is same or 11i and R12 but the look and feel and navigation is  completely different.
Q. Any profile for confidentiality of Quotation?
No. Anybody with access to quote and quote analysis screen has access to quotations
Q. when we delete approved record, if it is by other user, will he be notified for the same
No.
Q. How to protct cofidentialiy and modification of quot from others
– to prevent access to quote screen
– make the quote screen read only
Q. is there any work flow proces behind this?
no
Q. Can we have PO without RFQ or quotes for that matter.
Yes. We can have PO with RFQ and quotes. But its not a good practice.
Purchase Manager can be questioned why he gave order to some supplier. He will always have 2-3 quotes with him.
Q. but for blanket type of PO do you think quotes are required?
2 ways to determine price for an item
            – Quotations  – document provided by vendor to supply at a price
            – Blanket PO – agreement between vendor and us
Q. If we dont want other buyer can see qout of other buyer even they have access to RFQ screen ??
only related buyer only can see his Quot not other one which is not related to him ??
Personalization. Without personalization, its not possible.
Q. Can we copy Catalog RFQ to Standard quotation?
Yes. Copy Catalog RFQ to Catalaog quote or Standard quotation. Copy Bid RFQ to Bid quotation.
Q. Can you show us Creating PO from Requisition having RFQ as Source Doc.
RFQ cannot be source doc. We can have Quote as Source and we will be creating PO from Req using Quote as Source.
Q. Yes but not for release.
Question not clear
Q. What about negotion process in this ??
Negotiation is possible in Oracle Sourcing. Out of Scope.
Q. Are u going to take Planned Purchase order
Yes. That will be covered later.
Q. Will we cover Schedule release VMIand Consigned.?
Out of scope. Inventory related. Kindly attend Inventory training.
Q. Is there  any analytics available in this release to freeze the minimum quote automatically?
No. Quote analysis and approval is a manual process. No automation is provided.
Q. Can you cover the Advance Pricing intergration in Purchasing?
This will be covered conceptually. There wont be any practicals on the same.
Q. What about Bill of distribution??
Sourcing Rules  – ASL is required. Will cover Sourcing rules later.
Q. Sourcing means for RFQ ??
Selecting the Source of Supply of material.
Sourcing – Sourcing Rules – automatically create PO from requisitions
Q. While using ASL for Commodity theall items listed under that commodity will get imapcted . believe it’s true can u confirm .
Yes. I will show you guys a chart
Q. How the Supplier status behave differently in Global & Local ASL?
Local ASL will override Global
Local (M1) – debarred Global – Approved
PO for M1 – will not allow
PO for M2 – should allow
But local is specific to that org not for others. so for M2 it should allowed.
Q. what is category level supplier?
The supplier will be ASL for all items which fall under that category.
Q. then where do we define that?
That is defined in ASL
Q. Where do we define the range of items under that category?
Linked while defining Items
Q. AND is approved for Item not for category so Item shoud be presidance. and it should allowed. Item shoud have more presidance then category.
NO. Category is having higher precedence. So, if a Supplier is debarred for a category then he is debarred for all items under that category.
Q. Can we create Req to Release Item if is not matching on Requisition and BPA. but ASL is set.
Question is not clear
Q. what if allocation for ASL is <100 %
For manual PR, 100% allocation does not matter. 100% allocation is required only for planned orders. For manual PR the PO will be raised against the supplier with highest allocation.
Q. What does Sourcing Assignment signify?
Sourcing Assignment will determine which supplier to source an item for a given ORG
Q. If we have one org then can we do transaction in Master Org
Yes. No restriction. Best practice that we should not do any transaction in Master Inv Org.
Q. What isTCA??
Trading Community Architecture. Covered in AR. This is party, customer relationship and stored in HZ tables.
Q. How intercompany sourcing happens and how PO will be managed in two companies?
Internal Requisition/Internal Sales Orders
Q. What is the need to add the price into price list…when we have given price for the item in Inventory setup?
Price that we mentioned is the Purchase list price. But for OM, it picks the price from Price list.
Q. While giving the value in Price list…do we add our all the cost and profit in that
For internal items, the transfer price is calculated based on the cost + the transfer options.
Q. What does create internal order conc request do?
Create internal order request will transfer the IR info to OM interface tables.
Q. Why is the cost zero for Internal Orders?
We did not enter any cost for the item, so the cost was zero.
Q. could you please show this cost thing by raising external sales order?
Out of Scope. OM class is required.
Q. Cost is zero for internal sales order, however you told that it will be 10% addition of the cost of that item then why it is not 10 % addition in the cost?
Q. Why is the cost of the Item zero?
The cost of the Item was zero because we did not enter the cost of the item.
Q. Where do we enter the cost?
We enter the cost while defining the Item.
Q. How does the price default while creating IR?
The price of the item while creating IR, is the cost of the Item.
Q. Can you clarify where we set up zero cost for an Item for IR?
We did not specify the zero for IR. If we do not enter the cost, it will be considered as zero.
Q. Can we change PO Currency?
Yes. We can change PO currency. It defaults from supplier setup but we can change it.
Q. Can you explain what is confirming order ?
Sometimes, the Purchase manager requests for certain items over phone. Then he sends the PO just to confirm the verbal order. If the vendor thinks that this is a duplicate order and delivers double, it could cause problem, so he sends a PO with a message that it is confirming order.
What is Difference Between Blanket release and Planned Release?
Blanket release is against blanket purchase agreement and planned release is against planned orders.
Under what Circumstance we’ll use Blanket and Planned Agreement
In blanket, the price of the agreement is fixed but the quantity is not fixed. We get price breaks in Blanket for volume discounts.
In planned, the shipment is tentative. It does not have any price breaks so no volume discounts.
So, blanket is for high volume transactions.
Q. For planned orders, Schedule is available but blanket is for as and when required?
The schedule in planned Orders is just tentative. The actual schedule is determined later.
Blanket can have an effective date range. Planned does not have any effective date range.
Q. Can you cover Advance Pricing?
Not in scope.
Q. What is the way for adding items or modify BPA from supplier point of view..are they need have oracle apps installed with Oracle iSupplier portal?
They will have access to Oracle iSupplier portal which is a web based application. No installation is required.
/by
, , , , , ,

Payables Open Interface for Project based Invoices

Following scripts used to test Payable open Interface for Invoices contains Project Data:

 Query for to get PO details:
 –select * from po_headers_all  where  PO_HEADER_ID=63845
–AND segment1=’NEX060378-002′
 –select * from po_lines_all where po_header_id=63845
–select * from po_vendors where vendor_id=11813
 –select * from po_line_locations_all where po_header_id=63845
  –select *  from PO_DISTRIBUTIONS_ALL
 –select * from ap_invoice_lines_interface
 –commit
Query to Set Org_id: 
–begin
–dbms_application_info.set_client_info(‘169’);
–end;

–select * from ap_invoices_interface where invoice_num like ‘COL%’
–select * from ap_invoice_lines_interface
— select * from  AP_TERMS
–select * from PO_DISTRIBUTIONS_ALL

DECLARE
    p_invoice_id       NUMBER;
    i                  NUMBER;
BEGIN

Query to Generate invoice_id —

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

Query to  Insert an invoice header —
 insert into ap_invoices_interface(INVOICE_ID,
                 INVOICE_NUM,
                   INVOICE_TYPE_LOOKUP_CODE,
                  INVOICE_DATE,
                 PO_NUMBER,
                 VENDOR_ID,
                 VENDOR_SITE_ID,
                   INVOICE_AMOUNT,
                 INVOICE_CURRENCY_CODE,
                  –TERMS_ID,
                   SOURCE,
                   GROUP_ID,
                PAYMENT_METHOD_LOOKUP_CODE,
                ORG_ID)
        VALUES  (p_invoice_id,         –INVOICE_ID,
                  ‘COL012’,              –INVOICE_NUM,
                  ‘STANDARD’,            –INVOICE_TYPE_LOOKUP_CODE,
                  sysdate,              –INVOICE_DATE,
                 ‘NEX060378-002’,                    –v_PO_NUMBER,
                11934,                 –v_VENDOR_ID, 11790
                14667,                 –v_VENDOR_SITE_ID, 14506
                10,                       –v_INVOICE_AMOUNT,  (Sanjiv Wrong Amt)10
               ‘USD’,                  — v_INVOICE_CURRENCY_CODE, usd
                –10011,                  –v_TERMS_ID, (Sanjiv)
                ‘CONVERSION’,–‘INVOICE GATEWAY’,       –v_SOURCE,
                 ‘USM’,                  — v_GROUP_ID,
               ”,                     — v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
               169);                   –v_ORG_ID
    — Insert invoice line —

    for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
                                INVOICE_LINE_ID,
                                LINE_NUMBER,
                                LINE_TYPE_LOOKUP_CODE,
                                AMOUNT,
                                ACCOUNTING_DATE,
                                FINAL_MATCH_FLAG,
                                PO_HEADER_ID,
                                PO_NUMBER,
                                 PO_LINE_ID,
                                 PO_LINE_NUMBER,
                                PO_LINE_LOCATION_ID,
                                PO_SHIPMENT_NUM,
                                INVENTORY_ITEM_ID,
                                ITEM_DESCRIPTION,
                                QUANTITY_INVOICED,
                                SHIP_TO_LOCATION_CODE,
                                UNIT_PRICE,
                                PO_RELEASE_ID,
                                RELEASE_NUM,
                                PRICE_CORRECTION_FLAG,
                                RECEIPT_NUMBER,
                                MATCH_OPTION,
                                RCV_TRANSACTION_ID,
                                DIST_CODE_COMBINATION_ID,
                                PO_DISTRIBUTION_ID,
                                PO_DISTRIBUTION_NUM,
                                PROJECT_ID,
                                TASK_ID,
                                EXPENDITURE_TYPE,
                                EXPENDITURE_ORGANIZATION_ID,
                                PA_QUANTITY,
                                EXPENDITURE_ITEM_DATE
                                )
                        VALUES (
                                 p_invoice_id,     –v_INVOICE_ID,
                                 AP_INVOICE_LINES_INTERFACE_S.nextval,       –v_INVOICE_LINE_ID,
                                     i,               –v_LINE_NUMBER,  1
                                  ‘ITEM’,           — v_LINE_TYPE_LOOKUP_CODE,
                                   10,            –v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
                                  sysdate,           — v_ACCOUNTING_DATE,
                                  ”,                –v_FINAL_MATCH_FLAG,
                                  63845,                –v_PO_HEADER_ID,
                                 ‘NEX060378-002’,             — v_PO_NUMBER,
                                 63846,              –v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
                                  1,               –v_PO_LINE_NUMBER,
                                  63846,           –v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
                                    ”,                –v_PO_SHIPMENT_NUM,
                                  ”,                –v_INVENTORY_ITEM_ID,
                                  ”,                –v_ITEM_DESCRIPTION,
                                  1,                –v_QUANTITY_INVOICED,
                                  ”,                –v_SHIP_TO_LOCATION_CODE,
                                 ”,                 –v_UNIT_PRICE,
                                ”,                 –v_PO_RELEASE_ID,
                                ”,                 –v_RELEASE_NUM,
                                   ”,                 –v_PRICE_CORRECTION_FLAG,
                                ”,                 –v_RECEIPT_NUMBER,
                                   ”,                –v_MATCH_OPTION,
                                   ”,                 –v_RCV_TRANSACTION_ID
                                ”,           –v_DIST_CODE_COMBINATION_ID
                                64302,            –PO_DISTRIBUTION_ID = 64302
                                1,                  –PO_DISTRIBUTION_NUM
                                ”,–1087,               –PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
                                ”,–1551,                –TASK_ID,
                                ”,–‘Awrd Ovation Point Redemption’,–EXPENDITURE_TYPE,
                                 ”,–169,                –EXPENDITURE_ORGANIZATION_ID,
                                 ”,–”,             –PA_QUANTITY, (Sanjiv Wrong Qty)10
                              ” );– sysdate );           –EXPENDITURE_ITEM_DATE
      end loop;

 commit;
END;

Queries to debugged and trace Interface errors: 

–select * from ap_invoices_interface where invoice_id=13634
–select * from ap_invoice_lines_interface where invoice_id=13634
–select * from user_errors where name like’USM%’;
— select * from pa_projects_all where project_id=1172 –1087
–select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number=’2.4′)
–10437
–select * from usm_po_codes
–select * from pa_projects_all where segment1 =’10437′

/by
, , , , , ,

Oracle APPS Supply Chain Management tables ERD Diagram

Oracle inventory main reporting tables.
——————————————————————————–
MTL_MATERIAL_TRANSACTIONS
Base table for all transactions (etc RCV,Issuance,Sub Inv Transfer,Inter Org Transfer,Misl Transactions…).

MTL_SYSTEM_ITEMS
Table contains all System items organization wise. It is Base table for items.
This table holds the definitions for inventory items, engineering items, and purchasing items. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and the ORGANIZATION_ID. The same item could be defined in more than one organization. Each row represents an item in an organization.

MTL_SYSTEM_ITEMS_TL
Table contains translated description of each master item.
The primary key is INVENTORY_ITEM_ID, ORGANIZATION_ID and LANGUAGE.

MTL_TRANSACTION_TYPES
Transaction types and names.

ORG_ACCT_PERIODS
Organization wise accounting periods.

GL_CODE_COMBINATIONS
Stores valid Accounting Flexfield segment value combinations for each accounting flexfield structure within your General Ledger. Primary key: CODE_COMBINATION_ID.

MTL_SECONDARY_INVENTORIES
This table is the definition table for the subinventory. Subinventories are assigned to items, indicating a list of valid places in which this item may be located. The primary key is SECONDARY_INVENTORY_NAME, ORGANIZATION_ID.

 MTL_ITEM_CATEGORIES
This table stores the item assignments to categories within a category set. For each assignment, this table stores the item, category set, and the category. Items can be assigned to multiple categories and category sets, but can be assigned to only one category in a given category set. This table is populated through either the Define Item or the Update Item/Org Attributes forms. It can also be populated by performing item assignments when a category set is defined. The primary key is INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID.
 
Oracle Purchasing main reporting tables.
——————————————————————————–
RCV_TRANSACTIONS
You can get data of all receiving transactions in this table.
Important columns
TRANSACTION_TYPE like  DELIVER,REJECT,ACCEPT,CORRECT,RETURN TO RECEIVING
TRANSFER, RETURN TO VENDOR, RECEIVE.
Also you can directly connect to shipment and purchase order tables for rcv_transactions
By  SHIPMENT_HEADER_ID and PO_HEADER_ID.
PO_HEADERS_ALL
Get purchase order header level information from this table.
Important Columns
TYPE_LOOKUP_CODE indicates purchase order type like RFQ, STANDARD.
PO_LINES_ALL
Get purchase order live level information from this table.
Important columns
ITEM_ID join with table MTL_SYSTEM_ITEMS column ITEM_ID to get item all information.
PO_VENDORS
Get vendor level information from this table.
Important columns
PO_VENDOR_SITES_ALL
Get vendor sites information from this table in detail.

 

/by