Import thru PO Interface tables

*this article is in reference to standard PO only.
You need to insert records in three table sequencially:
1. PO_HEADERS_INTERFACE
2. PO_LINE_INTERFACE
3. PO_DISTRIBUTIONS_INTERFACE


-----------------------------------------------------
-- Inserting Validated Data Into PO_HEADERS_INTERFACE
-----------------------------------------------------

INSERT INTO po_headers_interface (interface_header_id,
process_code,
action,
org_id,
document_type_code,
document_num,
currency_code,
agent_id,
vendor_id,
vendor_site_code,
vendor_site_id,
ship_to_location_id,
bill_to_location_id,
comments,
vendor_doc_num,
freight_terms,
fob,
freight_carrier,
terms_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
effective_date,
expiration_date,
amount_agreed)
VALUES (po_headers_interface_s.NEXTVAL, -- INTERFACE_HEADER_ID
'PENDING',
'ORIGINAL', -- Type of action to be performed ACTION
ph.org_id, -- OU of the inventory org for which the data is bing provided.
ph.document_subtype, -- Fixed Value for loading standard PO
ph.po_number, -- PO number. In this case legacy PO no is uploaded here.
ph.currency_code, -- Validated Currency code from FND_Currencies
ph.agent_id,
ph.vendor_id,
ph.supplier_site,
ph.vendor_site_id,
ph.ship_to_location_id,
ph.bill_to_location_id,
ph.po_desc,
ph.po_number, -- PO number. In this case legacy PO no is uploaded here.
ph.freight_terms,
ph.fob, --PH.freight_carrier
ph.freight_code,
ph.terms_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
ph.effective_date,
ph.expiration_date,
ph.amount_agreed);

---------------------------------------------------
-- Inserting Validated Data into PO_LINES_INTERFACE
---------------------------------------------------
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_num,
item_id,
item_description,
uom_code,
unit_of_measure,
quantity,
unit_price,
ship_to_organization_id,
need_by_date,
promised_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
line_type,
vendor_product_num,
note_to_vendor,
shipment_num,
closed_code,
closed_reason,
CATEGORY,
price_break_lookup_code,
effective_date,
expiration_date,
list_price_per_unit)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
pl.line_num,
pl.item_id,
LTRIM (RTRIM (pl.item_description)),
pl.uom_code,
pl.unit_of_measure,
pl.quantity,
pl.unit_price,
pl.ship_to_organization_id,
NULL, --pl.NEED_BY_DATE,
NULL, --pl.PROMISED_DATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NULL, --pl.line_type,
LTRIM (RTRIM (pl.vendor_item)),
pl.note_to_vendor, --new added
NULL, --pl.SHIPMENT_NUMBER,
pl.closed_code,
pl.closed_reason,
pl.primary_category,
'NON CUMULATIVE',
pl.effective_date,
pl.expiration_date,
pl.list_price_per_unit);


---insert line for blanket location
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_num,
item_id,
item_description,
uom_code,
unit_of_measure,
quantity,
unit_price,
ship_to_organization_id,
need_by_date,
promised_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
line_type,
vendor_product_num,
note_to_vendor,
shipment_num,
closed_code,
closed_reason,
CATEGORY,
price_break_lookup_code,
effective_date,
expiration_date,
shipment_type)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
pl.line_num,
pl.item_id,
LTRIM (RTRIM (pl.item_description)),
pl.uom_code,
pl.unit_of_measure,
0, --QUANTITY,
pl.unit_price,
pl.ship_to_organization_id,
NULL, --pl.NEED_BY_DATE,
NULL, --pl.PROMISED_DATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NULL, -- pl.line_type,
LTRIM (RTRIM (pl.vendor_item)),
NULL,
pl.line_num, --pl.note_to_vendor
pl.closed_code,
pl.closed_reason,
pl.primary_category,
NULL,
pl.effective_date,
pl.expiration_date,
'PRICE BREAK');

-----------------------------------------------------------
-- Inserting Validated Data into PO_DISTRIBUTIONS_INTERFACE
-----------------------------------------------------------
INSERT INTO po_distributions_interface (interface_header_id,
interface_line_id,
interface_distribution_id,
po_header_id,
po_line_id,
distribution_num,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
destination_type_code,
deliver_to_location_id,
charge_account,
charge_account_id,
creation_date,
created_by)
VALUES (po_headers_interface_s.CURRVAL,
po_lines_interface_s.CURRVAL,
po_distributions_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
po_lines_interface_s.CURRVAL,
'1', -- distribution numbner
pl.quantity, -- ordered
NULL, -- delivered
NULL, -- billed
NULL, -- cancelled
pl.destination_type_code,
ph.ship_to_location_id,
pl.charge_account,
pl.charge_account_id,
SYSDATE,
fnd_global.user_id);

Commit;

After all this run program "Import Standard Purchase Orders"

Serial number TOKEN is defined in a different organization

If you get error "Serial number &TOKEN is defined in a different organization" for receiving any serial number.
Check the ORGANIZATION_ID for the item in MTL_SERIAL_NUMBERS table and change is accordingly. If this is correct then try for the SQL given below for applying datafix..


UPDATE mtl_serial_numbers
SET current_status = :Status, -- refer the statuses given below
group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE serial_number = :serial_number


LOOKUP_CODE MEANING
1 Defined but not used
3 Resides in stores
4 Issued out of stores
5 Resides in intransit
7 Resides in receiving
8 Resides in WIP

how to restrict sales of item to specific customer

Question:
Say I have 30 customers and suppose i want to sell my product for 2 customers only
I don't want to sell for others.
how can i do it?

Answer:
1. Make one group of customers to whom I want to sell the particular item.
2. On price-list form Qualifier (tab) add that group for that item line with desired context, attribute, operator, and value

BOM

Q. Why limited organizations are available in Change Organization window??
Ans: We need to assign organization to the application to access it from that application and the responsibility. Navigation is Inventory (R) --> Setups --> Organization --> Organization Access


Q: What is the use/meaning of Lead Time% in Routing (Main tab), count point, CUM Yield?
Ans: 1. Lead Time % is the % of total lead time taken for that particular operation.
2. Count Point is generally used in Project manufacturing. I am not sure about how does itr affect the functionality.
3. CUM Yield (Cumulative Yield): It is the addition of yield of all the previous operations.

Q. What is supply type=Supplier in BOM?
Ans: It is used for VMI (Vendor Managed Inventory). This is a concept from Lean manufacturing where some vendor/s are asked to maintain their inventory at our place, so that our inventory carrying cost goes down.