The PO is at OU level and Inventory is at Org level, so, from which organization the items LOV gets items. Below is the navigation to select the inventory organization in financial option under purchasing setup.
(SN) Purchasing > Setup > Organizations > Financial Options
(Tab) Supplier - Purchasing > (fl) Inventory Organization
This field holds the organization from which the items will be selected from purchasing order screen.
Query to know the inventory org used for purchasing OU:
SELECT hou.name,MP.ORGANIZATION_CODE "Inventory Organizaiton Code"
,HAOU.NAME "Inventory Organizaiton Name"
,MP.ORGANIZATION_CODE||' - '||HAOU.NAME "Front end display"
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS haou
, hr_operating_units hou
WHERE 1=1
and FSPA.ORG_ID = hou.organization_id
AND FSPA.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID
and mp.ORGANIZATION_ID = haou.ORGANIZATION_ID;
For any organization purchasing of materials/goods/services from suppliers is needed in order to fulfill their requirement. Oracle has implemented purchasing module to satisfy the needs of an organization. Below are the basic elements of purchasing.
Vendors: Supplier who provide services need to defined to purchase goods/services, this information will be available in AP_SUPPLIERS, AP_SUPPLIER_STIES_ALL, AP_SUPPLIER_CONTACTS.
Items: All purchasing items need to defined in inventory with appropriate attributes, these will be stored in MTL_SYSTEM_ITEMS_B
Buyer : A buyer who is an employee need to be setup in HRMS, this information will be available PER_ALL_PEOPLE_F table.
Below are the type of documents available in in purchasing, out of which you can add sub types to RFQ and Quotations.
Requisitions: This can be Internal or Purchase ( External ).
RFQ: There are three type of RFQs in purchasing, these are Standard, Catalog, and Bid.
Quotation : Again these are 3 types as in RFQ ( Standard, Catalog, and Bid).
Purchase Document: There are 4 types of purchasing document, these are Standard, Planned, Catalog, and Blanket.
Except requisitions all other documents stored in PO_HEADERS_ALL and PO_LINES_ALL tables.
The requisition information is stored in PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL table.
Standard PO:
One Time: This is generally used for one time purchasing.
Delivery Scheduled : It is available when creating.
Goods and Services: It is know to buyer when creating.
Price: It is know to buyer when creating.
Quantity: It is know to buyer when creating.
Planned PO:
This is used when organization wants to buy goods from a supplier for a long-terms with tentative delivery schedule.
Long Time: This is generally used for long time purchasing.
Delivery Scheduled : Tentative date is available when creating.
Goods and Services: It is know to buyer when creating.
Price: It is know to buyer when creating.
Quantity: It is know to buyer when creating
Contract PO:
Without knowing goods and services an organization make a contract with a supplier on
specific terms and conditions Later, a buyer in this organization issue a standard PO referring this contract PO, while raising standard PO buyer will provide all other details.
Blanket PO:
Internal Requisition uses item cost for price of an item whereas Supplier Requisition uses list price for price of an item.
PO Details Query:
select
pov.vendor_name
,poh.segment1 po_number
,poh.closed_code hdr_status
,poh.authorization_status
,pol.line_num
,trunc(pol.creation_date) line_creation_date
,pol.closed_code line_status
,msi.segment1 item_number
,msi.inventory_item_id
,msi.organization_id
,msi.description
,msi.planner_code planner
,ppf.full_name buyer
,pll.closed_code
,pll.promised_date
,pll.need_by_date
,nvl(pll.promised_date,pll.need_by_date)
due_date
,pll.quantity
,pll.quantity_received qty_rcvd
,pll.quantity_cancelled qty_canc
,(pll.quantity - pll.quantity_received - pll.quantity_cancelled)
open_qty
,pll.price_override price
,(pll.quantity - pll.quantity_received - pll.quantity_cancelled) * pll.price_override
ext_open_qty
,ood.organization_name inv_org_name
,ood.organization_id inv_org_id
from
po_headers_all poh
,po_lines_all pol
,po_line_locations_all pll
,po_vendors pov
,mtl_system_items_b msi
,per_all_people_f ppf
,org_organization_definitions ood
where
poh.po_header_id = pol.po_header_id
and pol.po_line_id = pll.po_line_id
and poh.vendor_id = pov.vendor_id
and poh.agent_id = ppf.person_id
and trunc(sysdate) between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date)
and pol.item_id = msi.inventory_item_id
and pol.org_id = msi.organization_id
and poh.org_id = ood.organization_id
and poh.type_lookup_code = 'STANDARD'
and ((poh.closed_code <> 'FINALLY CLOSED' and poh.closed_code <> 'CLOSED')
or
(poh.closed_code is null))
and ((pll.closed_code <> 'FINALLY CLOSED' and pll.closed_code <> 'CLOSED' and pll.closed_code <> 'CLOSED FOR RECEIVING')
or
(pll.closed_code is null))
and (pll.quantity - pll.quantity_received - pll.quantity_cancelled) > 0
order by
pov.vendor_name
,poh.segment1
,pol.line_num
,pll.line_location_id
Query for Open POs wating for approval.
Select b.segment1 po_number,b.authorization_status,a.message_name,a.status,a.notification_id,a.recipient_role,a.from_user,a.to_user,a.subject
from po_headers_all b,WF_NOTIFICATIONS a
where a.ITEM_KEY=B.WF_ITEM_KEY
and B.AUTHORIZATION_STATUS='IN PROCESS'
and Status='OPEN'
Query for Open Requisitions wating for approval.
select b.segment1 po_requisition_number,b.authorization_status,a.message_name,a.status,a.notification_id,a.recipient_role,a.from_user,a.to_user,a.subject
from PO_REQUISITION_HEADERS_ALL b,WF_NOTIFICATIONS a
where a.item_key=b.wf_item_key
and B.AUTHORIZATION_STATUS='IN PROCESS'
and a.Status='OPEN'
Select list of values from 'Actions' and write 'Reason' then click on 'OK' button, which will place the hold to that line.
You can automate this using below package procedure, this can be called from form personalization or make it a concurrent request.
CREATE OR REPLACE PACKAGE "APPS"."xxabc_RETURN_GOODS"
AS
PROCEDURE cancel_po_shipment_line( errbuf out varchar2
, retcode out varchar2
, f_action IN VARCHAR2
, f_document_id IN NUMBER
, f_document_type IN VARCHAR2
, f_document_subtype IN VARCHAR2
, f_line_id IN NUMBER
, f_shipment_id IN NUMBER
, f_org_id IN NUMBER
);
END xxabc_return_goods;
/
CREATE OR REPLACE PACKAGE BODY "APPS"."xxabc_RETURN_GOODS"
AS
PROCEDURE cancel_po_shipment_line ( errbuf out varchar2
, retcode out varchar2
, f_action IN VARCHAR2
, f_document_id IN NUMBER
, f_document_type IN VARCHAR2
, f_document_subtype IN VARCHAR2
, f_line_id IN NUMBER
, f_shipment_id IN NUMBER
, f_org_id IN NUMBER
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_return_status VARCHAR2(500);
l_return_code VARCHAR2(300);
l_exc_msg VARCHAR2(500);
l_online_report_id NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(500);
BEGIN
mo_global.set_policy_context('S',111);
PO_DOCUMENT_ACTION_PVT.do_manual_close(
p_action => f_action --'RECEIVE CLOSE'
, p_document_id => f_document_id
, p_document_type => f_document_type
, p_document_subtype => f_document_subtype
, p_line_id => f_line_id
, p_shipment_id => f_shipment_id
, p_reason => 'Close for receive - Shipment line will be closed for receiving'
, p_action_date => To_date(SYSDATE,'DD-MON-YYYY')
, p_calling_mode => 'PO'
, p_origin_doc_id => NULL
, p_called_from_conc => TRUE
, p_use_gl_date => 'N'
, x_return_status => l_return_status
, x_return_code => l_return_code
, x_exception_msg => l_exc_msg
, x_online_report_id => l_online_report_id
);
PO_DELREC_PVT.create_update_delrec
(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => f_action, --'RECEIVE CLOSE',
p_doc_type => f_document_type,
p_doc_subtype => f_document_subtype,
p_doc_id => f_document_id,
p_line_id => f_line_id,
p_line_location_id => f_shipment_id
);
COMMIT;
END cancel_po_shipment_line;
END xxabc_return_goods;
(SN) Purchasing > Setup > Organizations > Financial Options
(Tab) Supplier - Purchasing > (fl) Inventory Organization
This field holds the organization from which the items will be selected from purchasing order screen.
Query to know the inventory org used for purchasing OU:
SELECT hou.name,MP.ORGANIZATION_CODE "Inventory Organizaiton Code"
,HAOU.NAME "Inventory Organizaiton Name"
,MP.ORGANIZATION_CODE||' - '||HAOU.NAME "Front end display"
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS haou
, hr_operating_units hou
WHERE 1=1
and FSPA.ORG_ID = hou.organization_id
AND FSPA.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID
and mp.ORGANIZATION_ID = haou.ORGANIZATION_ID;
For any organization purchasing of materials/goods/services from suppliers is needed in order to fulfill their requirement. Oracle has implemented purchasing module to satisfy the needs of an organization. Below are the basic elements of purchasing.
Vendors: Supplier who provide services need to defined to purchase goods/services, this information will be available in AP_SUPPLIERS, AP_SUPPLIER_STIES_ALL, AP_SUPPLIER_CONTACTS.
Items: All purchasing items need to defined in inventory with appropriate attributes, these will be stored in MTL_SYSTEM_ITEMS_B
Buyer : A buyer who is an employee need to be setup in HRMS, this information will be available PER_ALL_PEOPLE_F table.
Below are the type of documents available in in purchasing, out of which you can add sub types to RFQ and Quotations.
Requisitions: This can be Internal or Purchase ( External ).
RFQ: There are three type of RFQs in purchasing, these are Standard, Catalog, and Bid.
Quotation : Again these are 3 types as in RFQ ( Standard, Catalog, and Bid).
Purchase Document: There are 4 types of purchasing document, these are Standard, Planned, Catalog, and Blanket.
Except requisitions all other documents stored in PO_HEADERS_ALL and PO_LINES_ALL tables.
The requisition information is stored in PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL table.
Standard PO:
One Time: This is generally used for one time purchasing.
Delivery Scheduled : It is available when creating.
Goods and Services: It is know to buyer when creating.
Price: It is know to buyer when creating.
Quantity: It is know to buyer when creating.
Planned PO:
This is used when organization wants to buy goods from a supplier for a long-terms with tentative delivery schedule.
Long Time: This is generally used for long time purchasing.
Delivery Scheduled : Tentative date is available when creating.
Goods and Services: It is know to buyer when creating.
Price: It is know to buyer when creating.
Quantity: It is know to buyer when creating
Contract PO:
Without knowing goods and services an organization make a contract with a supplier on
specific terms and conditions Later, a buyer in this organization issue a standard PO referring this contract PO, while raising standard PO buyer will provide all other details.
Blanket PO:
An organization knows goods and serviced to receive from a supplier, but did not know when(Delivery Schedule or need by date) these services are needed ( Eg: Mail service from UPS). So organization goes with Blanket PO with supplier. As and when need services, he will release a PO referring this blanket PO.
Internal Requisition uses item cost for price of an item whereas Supplier Requisition uses list price for price of an item.
select
pov.vendor_name
,poh.segment1 po_number
,poh.closed_code hdr_status
,poh.authorization_status
,pol.line_num
,trunc(pol.creation_date) line_creation_date
,pol.closed_code line_status
,msi.segment1 item_number
,msi.inventory_item_id
,msi.organization_id
,msi.description
,msi.planner_code planner
,ppf.full_name buyer
,pll.closed_code
,pll.promised_date
,pll.need_by_date
,nvl(pll.promised_date,pll.need_by_date)
due_date
,pll.quantity
,pll.quantity_received qty_rcvd
,pll.quantity_cancelled qty_canc
,(pll.quantity - pll.quantity_received - pll.quantity_cancelled)
open_qty
,pll.price_override price
,(pll.quantity - pll.quantity_received - pll.quantity_cancelled) * pll.price_override
ext_open_qty
,ood.organization_name inv_org_name
,ood.organization_id inv_org_id
from
po_headers_all poh
,po_lines_all pol
,po_line_locations_all pll
,po_vendors pov
,mtl_system_items_b msi
,per_all_people_f ppf
,org_organization_definitions ood
where
poh.po_header_id = pol.po_header_id
and pol.po_line_id = pll.po_line_id
and poh.vendor_id = pov.vendor_id
and poh.agent_id = ppf.person_id
and trunc(sysdate) between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date)
and pol.item_id = msi.inventory_item_id
and pol.org_id = msi.organization_id
and poh.org_id = ood.organization_id
and poh.type_lookup_code = 'STANDARD'
and ((poh.closed_code <> 'FINALLY CLOSED' and poh.closed_code <> 'CLOSED')
or
(poh.closed_code is null))
and ((pll.closed_code <> 'FINALLY CLOSED' and pll.closed_code <> 'CLOSED' and pll.closed_code <> 'CLOSED FOR RECEIVING')
or
(pll.closed_code is null))
and (pll.quantity - pll.quantity_received - pll.quantity_cancelled) > 0
order by
pov.vendor_name
,poh.segment1
,pol.line_num
,pll.line_location_id
Query for Open POs wating for approval.
Select b.segment1 po_number,b.authorization_status,a.message_name,a.status,a.notification_id,a.recipient_role,a.from_user,a.to_user,a.subject
from po_headers_all b,WF_NOTIFICATIONS a
where a.ITEM_KEY=B.WF_ITEM_KEY
and B.AUTHORIZATION_STATUS='IN PROCESS'
and Status='OPEN'
Query for Open Requisitions wating for approval.
select b.segment1 po_requisition_number,b.authorization_status,a.message_name,a.status,a.notification_id,a.recipient_role,a.from_user,a.to_user,a.subject
from PO_REQUISITION_HEADERS_ALL b,WF_NOTIFICATIONS a
where a.item_key=b.wf_item_key
and B.AUTHORIZATION_STATUS='IN PROCESS'
and a.Status='OPEN'
How to trigger Purchasing Hold
There is a situation when partial receiving is done, the purchase order shipment line should be on hold and it should not receive remaining quantity unless the hold is released.
Normally below is the manual procedure to put a purchase order on hold.
Go to Purchase Orders --> Purchase Order Summary, which opens 'Find Purchase Orders' form.
Enter PO# at 'Number' field and click 'Find' button.
Click 'Lines' button
Go to 'Tools' and select 'Control', which will open below form
You can automate this using below package procedure, this can be called from form personalization or make it a concurrent request.
CREATE OR REPLACE PACKAGE "APPS"."xxabc_RETURN_GOODS"
AS
PROCEDURE cancel_po_shipment_line( errbuf out varchar2
, retcode out varchar2
, f_action IN VARCHAR2
, f_document_id IN NUMBER
, f_document_type IN VARCHAR2
, f_document_subtype IN VARCHAR2
, f_line_id IN NUMBER
, f_shipment_id IN NUMBER
, f_org_id IN NUMBER
);
END xxabc_return_goods;
/
CREATE OR REPLACE PACKAGE BODY "APPS"."xxabc_RETURN_GOODS"
AS
PROCEDURE cancel_po_shipment_line ( errbuf out varchar2
, retcode out varchar2
, f_action IN VARCHAR2
, f_document_id IN NUMBER
, f_document_type IN VARCHAR2
, f_document_subtype IN VARCHAR2
, f_line_id IN NUMBER
, f_shipment_id IN NUMBER
, f_org_id IN NUMBER
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_return_status VARCHAR2(500);
l_return_code VARCHAR2(300);
l_exc_msg VARCHAR2(500);
l_online_report_id NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(500);
BEGIN
mo_global.set_policy_context('S',111);
PO_DOCUMENT_ACTION_PVT.do_manual_close(
p_action => f_action --'RECEIVE CLOSE'
, p_document_id => f_document_id
, p_document_type => f_document_type
, p_document_subtype => f_document_subtype
, p_line_id => f_line_id
, p_shipment_id => f_shipment_id
, p_reason => 'Close for receive - Shipment line will be closed for receiving'
, p_action_date => To_date(SYSDATE,'DD-MON-YYYY')
, p_calling_mode => 'PO'
, p_origin_doc_id => NULL
, p_called_from_conc => TRUE
, p_use_gl_date => 'N'
, x_return_status => l_return_status
, x_return_code => l_return_code
, x_exception_msg => l_exc_msg
, x_online_report_id => l_online_report_id
);
PO_DELREC_PVT.create_update_delrec
(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => f_action, --'RECEIVE CLOSE',
p_doc_type => f_document_type,
p_doc_subtype => f_document_subtype,
p_doc_id => f_document_id,
p_line_id => f_line_id,
p_line_location_id => f_shipment_id
);
COMMIT;
END cancel_po_shipment_line;
END xxabc_return_goods;
No comments:
Post a Comment