In this article I like to share some code samples to create a
purchase requisition related to a blanket, approve it, create a purchase order
from it and approve that. Then perform a receipt on the purchase order just
created.
Create Requisition
So the first step is to create the requisition. I have the
number start with a special prefix (XLS- by default, could be anything), so in
my AME I can approve these requisitions automatically. Here I'm assuming that
requisitions created this way should be approved automatically.
The header does nothing really but draw a new id.
----------------------------------------------------------------------------------------------
-- Create requisition header
----------------------------------------------------------------------------------------------
PROCEDURE Create_Requistion (
p_po_header_id IN NUMBER
, x_org_id OUT NUMBER
, x_requisition_header_id OUT NUMBER
, x_vendor_id OUT NUMBER
, x_vendor_site_id OUT NUMBER
, x_requisition_number OUT VARCHAR2
, x_error_code OUT VARCHAR2
, x_error_msg OUT VARCHAR2
)
IS
CURSOR C_Vendor
(
cp_header_id PO_HEADERS.PO_Header_Id%TYPE
)
IS
SELECT H.Vendor_Id
, H.Vendor_Site_Id
, H.Org_Id
FROM PO_HEADERS_ALL H
WHERE H.PO_Header_Id = cp_header_id
;
BEGIN
x_error_code := NULL;
x_requisition_number := NVL (FND_PROFILE.Value ('XXX_MY_PREFIX'),'XLS-') || to_char (Sysdate,'DDMMYYYYHH24MISS');
SELECT PO_REQUISITION_HEADERS_S.NEXTVAL
INTO x_requisition_header_id
FROM DUAL;
OPEN C_Vendor (cp_header_id => p_po_header_id);
FETCH C_Vendor INTO x_vendor_id, x_vendor_site_id,x_org_id;
CLOSE C_Vendor;
END Create_Requistion;
After that I create a loop to go through the items I like to add to my requisition. For each line we insert a record in PO_REQUISITIONS_INTERFACE_ALL. In our case the charge account is fetched based on some rules. Like the expense account segment comes from the item.
select r.segment_value
from mtl_item_categories_v c
, FND_ID_FLEX_STRUCTURES_VL s
, po_rule_expense_accounts r
where s.id_flex_num = c.structure_id
and s.id_flex_structure_code = 'PO_ITEM_CATEGORY'
and r.rule_value_id = c.category_id
and c.organization_id = (select master_organization_id from mtl_parameters where organization_id = m.organization_id)
and r.org_id = fnd_profile.value ('ORG_ID')
and c.inventory_item_id = m.inventory_item_id
By passing segments and allowing for dynamic insert you can create the account dynamically. Otherwise you could fetch the code_combination_id ofcourse.
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
, requisition_header_id
,req_number_segment1
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
--,charge_account_id
, charge_account_segment1
, charge_account_segment2
, charge_account_segment3
, charge_account_segment4
, charge_account_segment5
, charge_account_segment6
, charge_account_segment7
, charge_account_segment8
, charge_account_segment9
,accrual_account_id
--,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
, header_description
, category_id
--, category_segment1
, autosource_doc_header_id -- 24-Sep-2015
, autosource_doc_line_num -- 24-Sep-2015
, autosource_flag -- 24-Sep-2015
)
VALUES ('SCAN'
,'VENDOR'
,'PURCHASE'
,I.destination_type_code -- 'EXPENSE' -- depends on whether it's an article or not ..
,I.inventory_item_id
,l_description
,I.Quantity
,'INCOMPLETE'
,l_emp_id
, p_requisition_header_id
, p_requisition_number
, I.Primary_Unit_Of_Measure
, p_item_org_id
,null -- rec_get_lines_info.subinventory
, I.Location_Id -- rec_get_lines_info.location_id
,l_emp_id
,sysdate
,SYSDATE
, l_segment1
, l_segment2
, l_segment3
, l_segment4
, l_segment5
, l_segment6
, l_segment7
, l_segment8
, l_segment9
, null -- rec_get_lines_info.ap_accrual_account
, p_org_id
, p_vendor_id
, p_vendor_site_id
, COALESCE (I.Price_From_Blanket,l_cost_price,I.Unit_Price)
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
, 'Description ...'
, I.Category_Id
--, I.category_segment
, p_blanket_id
, p_blanket_line_num
, p_autosource_flag
);
Now after we have added the lines, we need to import it. So we submit the requisition import in PL/SQL using
l_request_id :=
fnd_request.submit_request (application => 'PO' --Application,
, program => 'REQIMPORT' --Program,
,argument1 => 'SCAN' --Interface Source code,
,argument2 => '' --Batch ID,
,argument3 => 'ALL'--Group By,
,argument4 => ''--Last Req Number,
,argument5 => 'N'--Multi Distributions,
,argument6 => 'Y' --Initiate Approval after ReqImport
);
COMMIT;
And since we want to continue with creating the PO after that, we wait until the request is finished using
l_conc_status := APPS.FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id => l_request_id
,interval => 5 -- Sleep 5 seconds between checks.
,max_wait => 600
,phase => l_phase
,status => l_status
,dev_phase => l_dev_phase
,dev_status => l_dev_status
,message => l_message
);
If the DEV_STATUS is not in ERROR, CANCELLED or TERMINATED, we continue.
The header does nothing really but draw a new id.
----------------------------------------------------------------------------------------------
-- Create requisition header
----------------------------------------------------------------------------------------------
PROCEDURE Create_Requistion (
p_po_header_id IN NUMBER
, x_org_id OUT NUMBER
, x_requisition_header_id OUT NUMBER
, x_vendor_id OUT NUMBER
, x_vendor_site_id OUT NUMBER
, x_requisition_number OUT VARCHAR2
, x_error_code OUT VARCHAR2
, x_error_msg OUT VARCHAR2
)
IS
CURSOR C_Vendor
(
cp_header_id PO_HEADERS.PO_Header_Id%TYPE
)
IS
SELECT H.Vendor_Id
, H.Vendor_Site_Id
, H.Org_Id
FROM PO_HEADERS_ALL H
WHERE H.PO_Header_Id = cp_header_id
;
BEGIN
x_error_code := NULL;
x_requisition_number := NVL (FND_PROFILE.Value ('XXX_MY_PREFIX'),'XLS-') || to_char (Sysdate,'DDMMYYYYHH24MISS');
SELECT PO_REQUISITION_HEADERS_S.NEXTVAL
INTO x_requisition_header_id
FROM DUAL;
OPEN C_Vendor (cp_header_id => p_po_header_id);
FETCH C_Vendor INTO x_vendor_id, x_vendor_site_id,x_org_id;
CLOSE C_Vendor;
END Create_Requistion;
After that I create a loop to go through the items I like to add to my requisition. For each line we insert a record in PO_REQUISITIONS_INTERFACE_ALL. In our case the charge account is fetched based on some rules. Like the expense account segment comes from the item.
select r.segment_value
from mtl_item_categories_v c
, FND_ID_FLEX_STRUCTURES_VL s
, po_rule_expense_accounts r
where s.id_flex_num = c.structure_id
and s.id_flex_structure_code = 'PO_ITEM_CATEGORY'
and r.rule_value_id = c.category_id
and c.organization_id = (select master_organization_id from mtl_parameters where organization_id = m.organization_id)
and r.org_id = fnd_profile.value ('ORG_ID')
and c.inventory_item_id = m.inventory_item_id
By passing segments and allowing for dynamic insert you can create the account dynamically. Otherwise you could fetch the code_combination_id ofcourse.
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
, requisition_header_id
,req_number_segment1
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
--,charge_account_id
, charge_account_segment1
, charge_account_segment2
, charge_account_segment3
, charge_account_segment4
, charge_account_segment5
, charge_account_segment6
, charge_account_segment7
, charge_account_segment8
, charge_account_segment9
,accrual_account_id
--,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
, header_description
, category_id
--, category_segment1
, autosource_doc_header_id -- 24-Sep-2015
, autosource_doc_line_num -- 24-Sep-2015
, autosource_flag -- 24-Sep-2015
)
VALUES ('SCAN'
,'VENDOR'
,'PURCHASE'
,I.destination_type_code -- 'EXPENSE' -- depends on whether it's an article or not ..
,I.inventory_item_id
,l_description
,I.Quantity
,'INCOMPLETE'
,l_emp_id
, p_requisition_header_id
, p_requisition_number
, I.Primary_Unit_Of_Measure
, p_item_org_id
,null -- rec_get_lines_info.subinventory
, I.Location_Id -- rec_get_lines_info.location_id
,l_emp_id
,sysdate
,SYSDATE
, l_segment1
, l_segment2
, l_segment3
, l_segment4
, l_segment5
, l_segment6
, l_segment7
, l_segment8
, l_segment9
, null -- rec_get_lines_info.ap_accrual_account
, p_org_id
, p_vendor_id
, p_vendor_site_id
, COALESCE (I.Price_From_Blanket,l_cost_price,I.Unit_Price)
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
, 'Description ...'
, I.Category_Id
--, I.category_segment
, p_blanket_id
, p_blanket_line_num
, p_autosource_flag
);
Now after we have added the lines, we need to import it. So we submit the requisition import in PL/SQL using
l_request_id :=
fnd_request.submit_request (application => 'PO' --Application,
, program => 'REQIMPORT' --Program,
,argument1 => 'SCAN' --Interface Source code,
,argument2 => '' --Batch ID,
,argument3 => 'ALL'--Group By,
,argument4 => ''--Last Req Number,
,argument5 => 'N'--Multi Distributions,
,argument6 => 'Y' --Initiate Approval after ReqImport
);
COMMIT;
And since we want to continue with creating the PO after that, we wait until the request is finished using
l_conc_status := APPS.FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id => l_request_id
,interval => 5 -- Sleep 5 seconds between checks.
,max_wait => 600
,phase => l_phase
,status => l_status
,dev_phase => l_dev_phase
,dev_status => l_dev_status
,message => l_message
);
If the DEV_STATUS is not in ERROR, CANCELLED or TERMINATED, we continue.
Verify Import
It's best to verify whether the import was OK using a procedure
like this.
-------------------------------------------------------------------------------
-- Verify requisition import
-------------------------------------------------------------------------------
PROCEDURE Verify_Import (
p_requisition_number IN VARCHAR
, x_req_header_id OUT NUMBER
, x_error_msg OUT VARCHAR2
)
IS
CURSOR C_Interface
IS
SELECT M.Segment1
, I.Item_Description
, I.Transaction_Id
FROM PO_REQUISITIONS_INTERFACE_ALL I
, MTL_SYSTEM_ITEMS_B M
WHERE I.Req_Number_Segment1 = p_requisition_number
AND I.Process_Flag = 'ERROR'
AND I.Item_Id = M.Inventory_Item_Id (+)
AND I.Destination_Organization_Id = NVL (M.Organization_Id,I.Destination_Organization_Id)
;
CURSOR C_Actual_ID
IS
SELECT Requisition_Header_Id
FROM PO_REQUISITION_HEADERS_ALL
WHERE Segment1 = p_requisition_number
;
CURSOR C_Errors
(
p_transaction_id NUMBER
)
IS
SELECT E.Error_Message
FROM PO_INTERFACE_ERRORS E
WHERE E.Interface_Transaction_Id = p_transaction_id
;
l_concat_msg VARCHAR2(240);
l_id NUMBER;
BEGIN
OPEN C_Actual_ID;
FETCH C_Actual_Id INTO l_id;
CLOSE C_Actual_Id;
IF l_id IS NOT NULL
THEN
-- Update some error record
null;
END IF;
<<interface>>
FOR I IN C_Interface
LOOP
x_error_msg := 'Error during import Req ' || p_requisition_number;
l_concat_msg := null;
<<Errors>>
FOR E IN C_Errors (p_transaction_id => I.Transaction_id)
LOOP
-- Print error message based on E.Error_message and item number
-- for example
NULL;
END LOOP Errors;
END LOOP Interface;
COMMIT;
END Verify_Import;
-------------------------------------------------------------------------------
-- Verify requisition import
-------------------------------------------------------------------------------
PROCEDURE Verify_Import (
p_requisition_number IN VARCHAR
, x_req_header_id OUT NUMBER
, x_error_msg OUT VARCHAR2
)
IS
CURSOR C_Interface
IS
SELECT M.Segment1
, I.Item_Description
, I.Transaction_Id
FROM PO_REQUISITIONS_INTERFACE_ALL I
, MTL_SYSTEM_ITEMS_B M
WHERE I.Req_Number_Segment1 = p_requisition_number
AND I.Process_Flag = 'ERROR'
AND I.Item_Id = M.Inventory_Item_Id (+)
AND I.Destination_Organization_Id = NVL (M.Organization_Id,I.Destination_Organization_Id)
;
CURSOR C_Actual_ID
IS
SELECT Requisition_Header_Id
FROM PO_REQUISITION_HEADERS_ALL
WHERE Segment1 = p_requisition_number
;
CURSOR C_Errors
(
p_transaction_id NUMBER
)
IS
SELECT E.Error_Message
FROM PO_INTERFACE_ERRORS E
WHERE E.Interface_Transaction_Id = p_transaction_id
;
l_concat_msg VARCHAR2(240);
l_id NUMBER;
BEGIN
OPEN C_Actual_ID;
FETCH C_Actual_Id INTO l_id;
CLOSE C_Actual_Id;
IF l_id IS NOT NULL
THEN
-- Update some error record
null;
END IF;
<<interface>>
FOR I IN C_Interface
LOOP
x_error_msg := 'Error during import Req ' || p_requisition_number;
l_concat_msg := null;
<<Errors>>
FOR E IN C_Errors (p_transaction_id => I.Transaction_id)
LOOP
-- Print error message based on E.Error_message and item number
-- for example
NULL;
END LOOP Errors;
END LOOP Interface;
COMMIT;
END Verify_Import;
Approve Requisition
Now we know the requisition was imported and it's OK, we can
approve it using this procedure.
-------------------------------------------------------------------------------
-- Approve the requisition
-------------------------------------------------------------------------------
PROCEDURE Approve_Requisition
(
p_requisition_number VARCHAR2
, x_msg OUT VARCHAR2
)
IS
l_msg WF_NOTIFICATIONS.Subject%TYPE;
CURSOR C_Wfl_Msg
(
p_item_key IN VARCHAR2
)
IS
SELECT Subject
FROM WF_NOTIFICATIONS
WHERE Item_Key = p_item_key
AND Message_Type = 'REQAPPRV'
;
CURSOR c_req_details
IS
SELECT prh.requisition_header_id,
prh.org_id,
prh.preparer_id,
prh.segment1,
pdt.document_subtype,
pdt.document_type_code,
prh.authorization_status
FROM apps.po_requisition_headers_all prh,
apps.po_document_types_all pdt
WHERE prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND pdt.document_type_code = 'REQUISITION'
AND NVL (authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
AND prh.segment1 = p_requisition_number; -- Enter The Requisition Number
v_item_key VARCHAR2(240);
BEGIN
<<req>>
FOR p_rec IN c_req_details
LOOP
mo_global.init ('PO');
mo_global.set_policy_context ('S', p_rec.org_id);
SELECT p_rec.requisition_header_id
|| '-'
|| TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM DUAL;
p_l ('Start Requisition approval ' || v_item_key);
po_reqapproval_init1.start_wf_process (
itemtype => NULL,
itemkey => v_item_key,
workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => p_rec.requisition_header_id, -- requisition_header_id
documentnumber => p_rec.segment1, -- Requisition Number
preparerid => p_rec.preparer_id,
documenttypecode => p_rec.document_type_code, -- REQUISITION
documentsubtype => p_rec.document_subtype, -- PURCHASE
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
COMMIT;
END LOOP Req;
-- Check workflow
OPEN C_WFL_Msg (p_item_key => v_item_key);
FETCH C_WFL_Msg INTO l_msg;
IF C_Wfl_Msg%FOUND
THEN
p_l ('Requisition Approval: ' || l_msg);
END IF;
CLOSE C_WFL_Msg;
END Approve_Requisition;
-------------------------------------------------------------------------------
-- Approve the requisition
-------------------------------------------------------------------------------
PROCEDURE Approve_Requisition
(
p_requisition_number VARCHAR2
, x_msg OUT VARCHAR2
)
IS
l_msg WF_NOTIFICATIONS.Subject%TYPE;
CURSOR C_Wfl_Msg
(
p_item_key IN VARCHAR2
)
IS
SELECT Subject
FROM WF_NOTIFICATIONS
WHERE Item_Key = p_item_key
AND Message_Type = 'REQAPPRV'
;
CURSOR c_req_details
IS
SELECT prh.requisition_header_id,
prh.org_id,
prh.preparer_id,
prh.segment1,
pdt.document_subtype,
pdt.document_type_code,
prh.authorization_status
FROM apps.po_requisition_headers_all prh,
apps.po_document_types_all pdt
WHERE prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND pdt.document_type_code = 'REQUISITION'
AND NVL (authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
AND prh.segment1 = p_requisition_number; -- Enter The Requisition Number
v_item_key VARCHAR2(240);
BEGIN
<<req>>
FOR p_rec IN c_req_details
LOOP
mo_global.init ('PO');
mo_global.set_policy_context ('S', p_rec.org_id);
SELECT p_rec.requisition_header_id
|| '-'
|| TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM DUAL;
p_l ('Start Requisition approval ' || v_item_key);
po_reqapproval_init1.start_wf_process (
itemtype => NULL,
itemkey => v_item_key,
workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => p_rec.requisition_header_id, -- requisition_header_id
documentnumber => p_rec.segment1, -- Requisition Number
preparerid => p_rec.preparer_id,
documenttypecode => p_rec.document_type_code, -- REQUISITION
documentsubtype => p_rec.document_subtype, -- PURCHASE
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
COMMIT;
END LOOP Req;
-- Check workflow
OPEN C_WFL_Msg (p_item_key => v_item_key);
FETCH C_WFL_Msg INTO l_msg;
IF C_Wfl_Msg%FOUND
THEN
p_l ('Requisition Approval: ' || l_msg);
END IF;
CLOSE C_WFL_Msg;
END Approve_Requisition;
Create Purchase Order based on
Requisition
The Requisition is created and approved, so we can create our PO
on it using the following procedure. In this case we also relate to a blanket
agreement. After creating the PO I updated the comments and notes to vendor,
since the system did not what I've added in the interface.
-------------------------------------------------------------------------------
-- Auto create the PO
-------------------------------------------------------------------------------
PROCEDURE Auto_Create_PO
(
p_requisition_number IN VARCHAR2
, p_po_header_id IN NUMBER
, x_po_number OUT VARCHAR2
, x_po_header_id OUT NUMBER
, x_error_msg OUT VARCHAR2
)
IS
cursor c_req_lines
is
select prha.segment1 req_num
, hla.ship_to_location_id
, prla.*
from po_requisition_headers_all prha
inner join po_requisition_lines_all prla
on prha.requisition_header_id = prla.requisition_header_id
inner join hr_locations_all hla
on prla.deliver_to_location_id = hla.location_id
where 1=1
and prha.authorization_status = 'APPROVED'
and nvl(prla.reqs_in_pool_flag,'N') = 'Y'
and nvl(prla.cancel_flag,'N') = 'N'
and nvl(prla.closed_code,'OPEN') = 'OPEN'
and prha.segment1 = p_requisition_number
order by hla.ship_to_location_id
, prla.creation_date desc
;
l_line_num number;
l_nr_of_lines number := 0;
l_shipment_num number;
l_prev_deliver_to_location_id po_requisition_lines_all.deliver_to_location_id%type;
l_prev_ship_to_location_id hr_locations_all.ship_to_location_id%type;
l_prev_blanket_po_header_id po_headers_all.po_header_id%type;
l_prev_blanket_po_line_num po_lines_all.line_num%type;
l_interface_header_id po_headers_interface.interface_header_id%type;
l_batch_id po_headers_interface.batch_id%type;
l_vendor_id po_headers_all.vendor_id%type;
l_vendor_site_id po_headers_all.vendor_site_id%type;
l_agent_id po_headers_all.agent_id%type;
l_org_id po_headers_all.org_id%type;
l_currency_code po_headers_all.currency_code%type;
l_bill_to_location_id po_headers_all.bill_to_location_id%type;
l_document_num po_headers_all.segment1%type;
l_last_updated_by po_headers_all.last_updated_by%type;
l_created_by po_headers_all.created_by%type;
l_interface_line_id po_lines_interface.interface_line_id%type;
l_promised_date po_line_locations_all.promised_date%type;
l_from_line_id po_lines_all.from_line_id%type;
l_consolidate ap_supplier_sites_all.attribute12%type;
x_return_status varchar2(1);
x_msg_count number;
x_msg_data fnd_new_messages.message_text%type;
x_document_num po_headers_all.segment1%type;
x_autocreated_doc_id po_headers_all.po_header_id%type;
x_num_lines_processed number;
l_header_created boolean := false;
BEGIN
x_error_msg := null;
l_line_num := 0;
p_l ('For all requisition lines ...' || p_requisition_number);
<<req>>
FOR i IN c_req_lines
LOOP
l_nr_of_lines := l_nr_of_lines + 1;
mo_global.init ('PO');
mo_global.set_policy_context ('S', i.org_id);
IF NOT l_header_created
THEN
l_header_created := true;
OPEN C_PO_Header (cp_header_id => p_po_header_id);
FETCH C_PO_Header INTO l_po_header;
CLOSE C_PO_Header;
l_vendor_id := l_po_header.vendor_id;
l_vendor_site_id := l_po_header.vendor_site_id;
l_agent_id := l_po_header.agent_id;
l_org_id := l_po_header.org_id;
l_currency_code := l_po_header.currency_code;
l_bill_to_location_id := l_po_header.bill_to_location_id;
l_consolidate := l_po_header.consolidate;
select po_headers_interface_s.nextval
,po_core_sv1.default_po_unique_identifier ('PO_HEADERS',l_org_id)
into l_interface_header_id
,l_document_num
from dual;
l_batch_id := l_interface_header_id;
p_l ('Interface header id is ' || l_interface_header_id || ' and org id is ' || l_org_id || ' and batch id is ' || l_batch_id);
insert into po_headers_interface
(
interface_header_id
, interface_source_code
, org_id
, batch_id
, process_code
, action
, document_type_code
, document_subtype
, document_num
, group_code
, vendor_id
, vendor_site_id
, agent_id
, currency_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, style_id
, Comments
)
values
(
l_interface_header_id
, 'PO'
, l_org_id
, l_batch_id
, 'NEW'
, 'NEW'
, 'PO'
, 'STANDARD'
, l_document_num
, 'REQUISITION' -- 'DEFAULT'
, l_vendor_id
, l_vendor_site_id
, l_agent_id
, l_currency_code
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, 1
, 'My description'
);
END IF; -- Only first time
select po_lines_interface_s.nextval
into l_interface_line_id
from dual;
l_shipment_num := 1;
l_line_num := l_line_num + 1;
--l_from_line_id := i.blanket_po_line_num;
l_promised_date := null;
IF i.blanket_po_line_num IS NOT NULL
THEN
BEGIN
SELECT PO_Line_Id
INTO l_from_line_id
FROM PO_LINES_ALL L
WHERE L.Line_Num = i.blanket_po_line_num
AND L.PO_header_Id = i.blanket_po_header_id
;
EXCEPTION
WHEN Others THEN
l_from_line_id := null;
END;
END IF;
p_l ('Link to blanket ' || i.blanket_po_header_id || ' line ' || i.blanket_po_line_num || ' with id ' || l_from_line_id);
insert into po_lines_interface
( interface_header_id
, interface_line_id
, requisition_line_id
, from_header_id
, from_line_id
, promised_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, line_num
, shipment_num
)
values
( l_interface_header_id
, l_interface_line_id
, i.requisition_line_id
, i.blanket_po_header_id
, l_from_line_id
, l_promised_date
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.User_id
, l_line_num
, l_shipment_num
);
COMMIT;
END LOOP Req;
p_l ('Auto create PO nr of lines ' || l_nr_of_lines);
IF l_nr_of_lines = 0
THEN
p_l ('ERROR: Requisition not approved.');
x_error_msg := 'Cannot find lines on requisition ' || p_requisition_number || ' that are OPEN and APPROVED.';
x_return_status := fnd_api.g_ret_Sts_error;
ELSE
po_interface_s.create_documents(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_batch_id => l_batch_id
,p_req_operating_unit_id => l_org_id
,p_purch_operating_unit_id => l_org_id
,x_document_id => x_autocreated_doc_id
,x_number_lines => x_num_lines_processed
,x_document_number => x_document_num
,p_document_creation_method => 'CREATEDOC'
,p_orig_org_id => l_org_id);
x_po_number := x_document_num;
x_po_header_id := x_autocreated_doc_id;
p_l ('Auto create PObatch ' || l_batch_id || ' and org id ' || l_org_id);
p_l ('Auto create PO' || x_document_num || ' status ' || x_return_status);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
x_error_msg := x_msg_data;
p_l ('Error creating PO: ' ||x_msg_data);
DELETE FROM PO_HEADERS_INTERFACE WHERE INterface_Header_Id = l_batch_id;
DELETE FROM PO_LINES_INTERFACE WHERE Interface_Header_Id = l_batch_id;
END IF;
END IF;
p_l ('');
END Auto_Create_PO;
-------------------------------------------------------------------------------
-- Auto create the PO
-------------------------------------------------------------------------------
PROCEDURE Auto_Create_PO
(
p_requisition_number IN VARCHAR2
, p_po_header_id IN NUMBER
, x_po_number OUT VARCHAR2
, x_po_header_id OUT NUMBER
, x_error_msg OUT VARCHAR2
)
IS
cursor c_req_lines
is
select prha.segment1 req_num
, hla.ship_to_location_id
, prla.*
from po_requisition_headers_all prha
inner join po_requisition_lines_all prla
on prha.requisition_header_id = prla.requisition_header_id
inner join hr_locations_all hla
on prla.deliver_to_location_id = hla.location_id
where 1=1
and prha.authorization_status = 'APPROVED'
and nvl(prla.reqs_in_pool_flag,'N') = 'Y'
and nvl(prla.cancel_flag,'N') = 'N'
and nvl(prla.closed_code,'OPEN') = 'OPEN'
and prha.segment1 = p_requisition_number
order by hla.ship_to_location_id
, prla.creation_date desc
;
l_line_num number;
l_nr_of_lines number := 0;
l_shipment_num number;
l_prev_deliver_to_location_id po_requisition_lines_all.deliver_to_location_id%type;
l_prev_ship_to_location_id hr_locations_all.ship_to_location_id%type;
l_prev_blanket_po_header_id po_headers_all.po_header_id%type;
l_prev_blanket_po_line_num po_lines_all.line_num%type;
l_interface_header_id po_headers_interface.interface_header_id%type;
l_batch_id po_headers_interface.batch_id%type;
l_vendor_id po_headers_all.vendor_id%type;
l_vendor_site_id po_headers_all.vendor_site_id%type;
l_agent_id po_headers_all.agent_id%type;
l_org_id po_headers_all.org_id%type;
l_currency_code po_headers_all.currency_code%type;
l_bill_to_location_id po_headers_all.bill_to_location_id%type;
l_document_num po_headers_all.segment1%type;
l_last_updated_by po_headers_all.last_updated_by%type;
l_created_by po_headers_all.created_by%type;
l_interface_line_id po_lines_interface.interface_line_id%type;
l_promised_date po_line_locations_all.promised_date%type;
l_from_line_id po_lines_all.from_line_id%type;
l_consolidate ap_supplier_sites_all.attribute12%type;
x_return_status varchar2(1);
x_msg_count number;
x_msg_data fnd_new_messages.message_text%type;
x_document_num po_headers_all.segment1%type;
x_autocreated_doc_id po_headers_all.po_header_id%type;
x_num_lines_processed number;
l_header_created boolean := false;
BEGIN
x_error_msg := null;
l_line_num := 0;
p_l ('For all requisition lines ...' || p_requisition_number);
<<req>>
FOR i IN c_req_lines
LOOP
l_nr_of_lines := l_nr_of_lines + 1;
mo_global.init ('PO');
mo_global.set_policy_context ('S', i.org_id);
IF NOT l_header_created
THEN
l_header_created := true;
OPEN C_PO_Header (cp_header_id => p_po_header_id);
FETCH C_PO_Header INTO l_po_header;
CLOSE C_PO_Header;
l_vendor_id := l_po_header.vendor_id;
l_vendor_site_id := l_po_header.vendor_site_id;
l_agent_id := l_po_header.agent_id;
l_org_id := l_po_header.org_id;
l_currency_code := l_po_header.currency_code;
l_bill_to_location_id := l_po_header.bill_to_location_id;
l_consolidate := l_po_header.consolidate;
select po_headers_interface_s.nextval
,po_core_sv1.default_po_unique_identifier ('PO_HEADERS',l_org_id)
into l_interface_header_id
,l_document_num
from dual;
l_batch_id := l_interface_header_id;
p_l ('Interface header id is ' || l_interface_header_id || ' and org id is ' || l_org_id || ' and batch id is ' || l_batch_id);
insert into po_headers_interface
(
interface_header_id
, interface_source_code
, org_id
, batch_id
, process_code
, action
, document_type_code
, document_subtype
, document_num
, group_code
, vendor_id
, vendor_site_id
, agent_id
, currency_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, style_id
, Comments
)
values
(
l_interface_header_id
, 'PO'
, l_org_id
, l_batch_id
, 'NEW'
, 'NEW'
, 'PO'
, 'STANDARD'
, l_document_num
, 'REQUISITION' -- 'DEFAULT'
, l_vendor_id
, l_vendor_site_id
, l_agent_id
, l_currency_code
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, 1
, 'My description'
);
END IF; -- Only first time
select po_lines_interface_s.nextval
into l_interface_line_id
from dual;
l_shipment_num := 1;
l_line_num := l_line_num + 1;
--l_from_line_id := i.blanket_po_line_num;
l_promised_date := null;
IF i.blanket_po_line_num IS NOT NULL
THEN
BEGIN
SELECT PO_Line_Id
INTO l_from_line_id
FROM PO_LINES_ALL L
WHERE L.Line_Num = i.blanket_po_line_num
AND L.PO_header_Id = i.blanket_po_header_id
;
EXCEPTION
WHEN Others THEN
l_from_line_id := null;
END;
END IF;
p_l ('Link to blanket ' || i.blanket_po_header_id || ' line ' || i.blanket_po_line_num || ' with id ' || l_from_line_id);
insert into po_lines_interface
( interface_header_id
, interface_line_id
, requisition_line_id
, from_header_id
, from_line_id
, promised_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, line_num
, shipment_num
)
values
( l_interface_header_id
, l_interface_line_id
, i.requisition_line_id
, i.blanket_po_header_id
, l_from_line_id
, l_promised_date
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.User_id
, l_line_num
, l_shipment_num
);
COMMIT;
END LOOP Req;
p_l ('Auto create PO nr of lines ' || l_nr_of_lines);
IF l_nr_of_lines = 0
THEN
p_l ('ERROR: Requisition not approved.');
x_error_msg := 'Cannot find lines on requisition ' || p_requisition_number || ' that are OPEN and APPROVED.';
x_return_status := fnd_api.g_ret_Sts_error;
ELSE
po_interface_s.create_documents(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_batch_id => l_batch_id
,p_req_operating_unit_id => l_org_id
,p_purch_operating_unit_id => l_org_id
,x_document_id => x_autocreated_doc_id
,x_number_lines => x_num_lines_processed
,x_document_number => x_document_num
,p_document_creation_method => 'CREATEDOC'
,p_orig_org_id => l_org_id);
x_po_number := x_document_num;
x_po_header_id := x_autocreated_doc_id;
p_l ('Auto create PObatch ' || l_batch_id || ' and org id ' || l_org_id);
p_l ('Auto create PO' || x_document_num || ' status ' || x_return_status);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
x_error_msg := x_msg_data;
p_l ('Error creating PO: ' ||x_msg_data);
DELETE FROM PO_HEADERS_INTERFACE WHERE INterface_Header_Id = l_batch_id;
DELETE FROM PO_LINES_INTERFACE WHERE Interface_Header_Id = l_batch_id;
END IF;
END IF;
p_l ('');
END Auto_Create_PO;
Approve Purchase Order
So next step is to approve the purchase order.
-------------------------------------------------------------------------------
-- Approve the Purchase Order
-------------------------------------------------------------------------------
PROCEDURE Approve_PO
(
p_po_number VARCHAR2
)
IS
v_item_key VARCHAR2 (100);
CURSOR c_po_details
IS
SELECT pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
nvl (pha.authorization_status,'INCOMPLETE') authorization_status
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND nvl (pha.authorization_status,'INCOMPLETE') IN
('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND segment1 = p_po_number; -- Enter the Purchase Order Number
BEGIN
p_l ('Goedkeuring controleren voor order ' || p_po_number);
<<PO>>
FOR p_rec IN c_po_details
LOOP
mo_global.init (p_rec.document_type_code);
mo_global.set_policy_context ('S', p_rec.org_id);
SELECT p_rec.po_header_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM DUAL;
p_l ('Goedkeuring workflow gestart ' || v_item_key);
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV',
itemkey => v_item_key,
workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => p_rec.po_header_id, -- po_header_id
documentnumber => p_rec.segment1, -- Purchase Order Number
preparerid => p_rec.agent_id, -- Buyer/Preparer_id
documenttypecode => p_rec.document_type_code, --'PO'
documentsubtype => p_rec.document_subtype, --'STANDARD'
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
COMMIT;
END LOOP PO;
END Approve_PO;
-------------------------------------------------------------------------------
-- Approve the Purchase Order
-------------------------------------------------------------------------------
PROCEDURE Approve_PO
(
p_po_number VARCHAR2
)
IS
v_item_key VARCHAR2 (100);
CURSOR c_po_details
IS
SELECT pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
nvl (pha.authorization_status,'INCOMPLETE') authorization_status
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND nvl (pha.authorization_status,'INCOMPLETE') IN
('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND segment1 = p_po_number; -- Enter the Purchase Order Number
BEGIN
p_l ('Goedkeuring controleren voor order ' || p_po_number);
<<PO>>
FOR p_rec IN c_po_details
LOOP
mo_global.init (p_rec.document_type_code);
mo_global.set_policy_context ('S', p_rec.org_id);
SELECT p_rec.po_header_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM DUAL;
p_l ('Goedkeuring workflow gestart ' || v_item_key);
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV',
itemkey => v_item_key,
workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => p_rec.po_header_id, -- po_header_id
documentnumber => p_rec.segment1, -- Purchase Order Number
preparerid => p_rec.agent_id, -- Buyer/Preparer_id
documenttypecode => p_rec.document_type_code, --'PO'
documentsubtype => p_rec.document_subtype, --'STANDARD'
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
COMMIT;
END LOOP PO;
END Approve_PO;
Receive on Purchase Order
And in our case we also wanted to perform a receipt on the PO
just created.
First you insert a record in the receiving header interface.
insert into rcv_headers_interface
(
header_interface_id,
group_id,
vendor_id,
vendor_site_id,
receipt_num,
receipt_header_id,
asn_type,
shipped_date,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
shipment_num,
ship_to_organization_id,
expected_receipt_date,
num_of_containers,
packing_slip,
validation_flag,
waybill_airbill_num
)
values
(
l_header_id,
l_group_id,
l_po_info.vendor_id,
l_po_info.vendor_site_id,
null,
null,
'ASN',
sysdate,
'PENDING',
'VENDOR',
'NEW',
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
l_shipment_num,
l_po_info.ship_to_organization_id,
sysdate,
1,
'SYSTEM',
'Y',
null
);
Then for each line you like to receive
INSERT INTO rcv_transactions_interface
(
Interface_transaction_id
, Group_Id
, Last_Update_Date
, Last_Updated_By
, Creation_Date
, Created_By
, Last_Update_Login
, Transaction_Type
, Transaction_Date
, Processing_Status_Code
, Processing_Mode_Code
, Transaction_STatus_Code
, Quantity
, UOM_Code
, Interface_Source_Code
, Item_Id
, Employee_Id
--, Shipment_Header_Id
--, Shipment_Line_Id
, PO_Header_Id
, PO_Line_Id
, PO_Line_Location_Id
, Receipt_Source_Code
, To_Organization_Id
, Source_Document_Code
, Destination_Type_Code
, Expected_Receipt_Date
, Header_Interface_Id
, Validation_Flag
, Attribute1
)
VALUES
(
l_interface_trx_id
, l_group_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, 'RECEIVE'
, l_item_info.transaction_date
, 'PENDING'
, l_mode
, 'PENDING'
, 1
, l_item_info.uom_code
, 'RCV'
, l_item_info.item_id
, l_item_info.employee_id
--, l_item_info.shipment_header_id
--, l_item_info.shipment_line_id
, l_item_info.po_header_id
, l_item_info.po_line_id
, l_item_info.po_line_location_id
, l_item_info.Receipt_source_code
, p_item_org_id
, l_item_info.Source_Document_Code
, l_item_info.Destination_Type_Code
, l_item_info.expected_Receipt_date
, l_header_id
, 'Y'
, p_attribute1
);
And finally run the transaction manager
XXP4_VB_RECEIPTS_PKG.call_txn_manager ( p_group_id => l_group_id
, p_error_code => l_error_code
, p_error_message => l_error_message
);
First you insert a record in the receiving header interface.
insert into rcv_headers_interface
(
header_interface_id,
group_id,
vendor_id,
vendor_site_id,
receipt_num,
receipt_header_id,
asn_type,
shipped_date,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
shipment_num,
ship_to_organization_id,
expected_receipt_date,
num_of_containers,
packing_slip,
validation_flag,
waybill_airbill_num
)
values
(
l_header_id,
l_group_id,
l_po_info.vendor_id,
l_po_info.vendor_site_id,
null,
null,
'ASN',
sysdate,
'PENDING',
'VENDOR',
'NEW',
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
l_shipment_num,
l_po_info.ship_to_organization_id,
sysdate,
1,
'SYSTEM',
'Y',
null
);
Then for each line you like to receive
INSERT INTO rcv_transactions_interface
(
Interface_transaction_id
, Group_Id
, Last_Update_Date
, Last_Updated_By
, Creation_Date
, Created_By
, Last_Update_Login
, Transaction_Type
, Transaction_Date
, Processing_Status_Code
, Processing_Mode_Code
, Transaction_STatus_Code
, Quantity
, UOM_Code
, Interface_Source_Code
, Item_Id
, Employee_Id
--, Shipment_Header_Id
--, Shipment_Line_Id
, PO_Header_Id
, PO_Line_Id
, PO_Line_Location_Id
, Receipt_Source_Code
, To_Organization_Id
, Source_Document_Code
, Destination_Type_Code
, Expected_Receipt_Date
, Header_Interface_Id
, Validation_Flag
, Attribute1
)
VALUES
(
l_interface_trx_id
, l_group_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, 'RECEIVE'
, l_item_info.transaction_date
, 'PENDING'
, l_mode
, 'PENDING'
, 1
, l_item_info.uom_code
, 'RCV'
, l_item_info.item_id
, l_item_info.employee_id
--, l_item_info.shipment_header_id
--, l_item_info.shipment_line_id
, l_item_info.po_header_id
, l_item_info.po_line_id
, l_item_info.po_line_location_id
, l_item_info.Receipt_source_code
, p_item_org_id
, l_item_info.Source_Document_Code
, l_item_info.Destination_Type_Code
, l_item_info.expected_Receipt_date
, l_header_id
, 'Y'
, p_attribute1
);
And finally run the transaction manager
XXP4_VB_RECEIPTS_PKG.call_txn_manager ( p_group_id => l_group_id
, p_error_code => l_error_code
, p_error_message => l_error_message
);
