CREATE OR REPLACE PACKAGE BODY APPS.xxspanvi_req_po_load
IS
PROCEDURE MAIN (errbuf OUT varchar2,
retcode OUT number,
p_batch_id number)
IS
CURSOR c_requistion_rec IS
SELECT *
FROM
xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id
and batch_id not in (select substr(attribute14,12) from po_requisition_lines_all);
l_error_flag varchar2 (1);
l_error_msg varchar2 (1000);
l_record_id number ;
l_item_id number ;
l_emp_id number ;
l_location_id number ;
l_organization_id number;
l_need_by_date date;
l_org_id number;
l_vendor_id number;
l_site_id number;
l_requestor_id number;
l_chanrge_account_id number;
--l_request_stat varchar2 (10);
BEGIN
BEGIN
SELECT NVL(MAX(record_id),0)
INTO l_record_id
FROM xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id;
UPDATE xxspanv_po_req_preinteface
SET record_id=l_record_id+ROWNUM;
COMMIT;
END;
fnd_file.put_line(fnd_file.log,'Validation started for stageging table');
FOR v_requistion_rec IN c_requistion_rec LOOP
----initializtion local varibale to null-----------
l_error_flag := null;
l_error_msg := null;
l_item_id :=null ;
l_emp_id :=null;
l_location_id :=null;
l_organization_id :=null;
l_need_by_date :=null;
l_org_id :=null;
l_vendor_id :=null;
l_site_id :=null;
l_requestor_id :=null;
l_chanrge_account_id :=null;
-----------VALIDATION STARTS HERE------------------
IF v_requistion_rec.INTERFACE_SOURCE_CODE IS NULL THEN
l_error_flag := 'E';
l_error_msg := 'INTERFACE_SOURCE_CODE Can not be Null';
END IF;
IF v_requistion_rec.requisition_type IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'REQUISITION_TYPE Can not be Null';
END IF;
IF v_requistion_rec.destination_type_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_TYPE_CODE Can not be Null';
END IF;
IF v_requistion_rec.item_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'ITEM_NAME Can not be Null';
ELSE
BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1)=UPPER(v_requistion_rec.item_name);
EXCEPTION WHEN others THEN
-- l_item_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'error occure while retreiving ITEM id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.quantity IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'QUANTITY Can not be Null';
END IF;
IF v_requistion_rec.preparer_full_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
ELSE
BEGIN
SELECT DISTINCT per.person_id
INTO l_emp_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.preparer_full_name )
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;
EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving emp_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.uom_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
END IF;
IF v_requistion_rec.destination_organization IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_ORGANIZATION Can not be Null';
ELSE
BEGIN
SELECT organization_id
INTO l_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE UPPER(ORGANIZATION_NAME)=UPPER(v_requistion_rec.DESTINATION_ORGANIZATION);
EXCEPTION WHEN OTHERS THEN
--l_organization_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Eroor occure while retreiving inventory org_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.deliver_to_location IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_LOCATION can not be null';
ELSE
BEGIN
SELECT location_id
INTO l_location_id
FROM HR_LOCATIONS
WHERE UPPER(location_code)=UPPER(v_requistion_rec.deliver_to_location);
EXCEPTION WHEN OTHERS THEN
--l_location_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving the deliver_to_location_id'||SQLERRM;
END ;
END IF;
IF v_requistion_rec.deliver_to_requestor IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_REQUESTOR can not be null';
ELSE
BEGIN
SELECT per.person_id
INTO l_requestor_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.deliver_to_requestor)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;
EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving requestor_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.charge_account IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'CHARGE_ACCOUNT can not be null';
ELSE
BEGIN
SELECT DISTINCT code_combination_id
INTO
l_chanrge_account_id
FROM gl_code_combinations_kfv a,
GL_SETS_OF_BOOKS gl
WHERE concatenated_segments=v_requistion_rec.charge_account
AND gl.chart_of_accounts_id=a.chart_of_accounts_id
AND gl.set_of_books_id=fnd_profile.value('gl_set_of_bks_id') ;
EXCEPTION WHEN OTHERS THEN
--l_chanrge_account_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the charge_account_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.need_by_date IS NULL THEN
l_need_by_date:= SYSDATE+1;
ELSE l_need_by_date:=v_requistion_rec.need_by_date;
END IF;
IF v_requistion_rec.ou_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'OU_NAME can not be null';
ELSE
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE UPPER(NAME)=UPPER(v_requistion_rec.ou_name);
EXCEPTION WHEN OTHERS THEN
--l_org_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the org_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.unit_price IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'UNIT_PRICE can not be null';
END IF;
IF v_requistion_rec.suggested_vendor_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_NAME can not be null';
ELSE
BEGIN
SELECT vendor_id
INTO
l_vendor_id
FROM AP_SUPPLIERS
WHERE UPPER(vendor_name)=UPPER(v_requistion_rec.suggested_vendor_name);
EXCEPTION WHEN OTHERS
THEN
--l_vendor_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the vendor_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.suggested_vendor_site IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_SITE can not be null';
ELSE
BEGIN
SELECT vendor_site_id
INTO l_site_id
FROM ap_supplier_sites
WHERE UPPER(vendor_site_code)=UPPER(v_requistion_rec.suggested_vendor_site)
AND vendor_id=l_vendor_id
AND org_id=l_org_id;
EXCEPTION WHEN OTHERS THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the site_id'||SQLERRM;
END;
END IF;
-------VALIDATION ENDS HERE------------------
------STRTING THE INSERTION IN REQUSITION INTERFACE -------------------------
IF l_error_flag IS NULL THEN
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(batch_id
,TRANSACTION_ID
,interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
--,category_id
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,charge_account_id
,need_by_date
,org_id
,unit_price
,autosource_flag
,line_attribute13
,line_attribute14
,suggested_vendor_id
,suggested_vendor_site_id
)
values
(v_requistion_rec.batch_id
,PO_REQUISITIONS_INTERFACE_S.nextval
,'FUTURE'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,l_item_id
,v_requistion_rec.item_description
,v_requistion_rec.quantity
,'INCOMPLETE'
,l_emp_id
,v_requistion_rec.uom_code
,l_organization_id
,v_requistion_rec.destination_subinventory
,l_location_id
,l_requestor_id
,l_chanrge_account_id
,l_need_by_date
,l_org_id
,v_requistion_rec.unit_price
,NULL
,v_requistion_rec.record_id
,'REQ_WRAPPER'||p_batch_id
,l_vendor_id
,l_site_id
);
ELSE
UPDATE xxspanv_po_req_preinteface
SET ERROR_FLAG=l_error_flag,
ERROR_MSG =l_error_msg
WHERE batch_id=v_requistion_rec.batch_id
AND record_id =v_requistion_rec.record_id;
END IF;
COMMIT;
END LOOP;
SUBMIT_REQUEST (P_BATCH_ID);
END MAIN;
PROCEDURE SUBMIT_REQUEST (P_BATCH_ID IN NUMBER --,
--STATUS OUT VARCHAR2
)
IS
l_request_id number;
l_max_wait number;
l_phase varchar2(10);
l_status varchar2(10);
l_dev_status varchar2(10);
l_message varchar2(100);
l_interval number;
l_dev_phase varchar2(10);
rphase varchar2(10);
rstatus varchar2(10);
dphase varchar2(10);
dstatus varchar2(10);
message varchar2(100);
call_status boolean ;
wait_status boolean ;
BEGIN
fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);
l_request_id:= fnd_request.submit_request('PO'
,'REQIMPORT'
,NULL
,NULL
,FALSE
,NULL---arg1
,p_batch_id
,'VENDOR'
,NULL
,NULL
,'Y'
);
COMMIT;
wait_status:=fnd_concurrent.wait_for_request (l_request_id,
60 ,
0,
l_phase ,
l_status ,
l_dev_phase,
l_dev_status,
L_message);
-- call_status :=fnd_concurrent.get_request_status(l_request_id, '', '',
-- rphase,rstatus,dphase,dstatus, message);
fnd_file.put_line(fnd_file.log,'dphase = '||l_dev_phase||'and '||'dstatus ='||l_dev_status) ;
IF UPPER(l_dev_phase)='COMPLETE' AND UPPER(l_dev_status)= 'NORMAL' THEN
dbms_output.put_line ('calling the procuedure to create po');----calling the po_creation procedure
fnd_file.put_line(fnd_file.log,'calling the procuedure to create po');
CREATE_PO (P_BATCH_ID);
END IF;
exception when others
then
fnd_file.put_line(fnd_file.log,'Error occure in procedure submit_request'||SQLERRM);
END SUBMIT_REQUEST;
----to create po using aoutocreate need to insert all approved requisions into po interfaces tables and then calling the api--
PROCEDURE CREATE_PO (P_BATCH_ID IN number)
IS
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(5000);
l_num_lines_processed number;
l_document_number po_headers_all.segment1%TYPE;
l_document_id number;
l_msg varchar2(5000);
l_msg_index_out number;
CURSOR po_interface_headers_rec IS
SELECT distinct prl.requisition_header_id
,prl.vendor_id
,prl.vendor_site_id
,prl.attribute14
--,prl.unit_meas_lookup_code
,prl.destination_organization_id
--,prl.org_id
,prh.org_id
--,prl.deliver_to_location_id
,prh.preparer_id
--,prh.authorization_status
,xprp.ship_to_location
,xprp.bill_to_location
--,xprp.item_name
,xprp.suggested_vendor_name
,xprp.suggested_vendor_site
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
xxspanv_po_req_preinteface xprp
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND to_char(xprp.batch_id)=substr(prl.attribute14,12)
--AND to_char(xprp.record_id)=(prl.ATTRIBUTE13)
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id;
CURSOR po_interface_lines_rec (p_requistion_header_id number)
IS
SELECT prl.requisition_header_id
,prl.quantity
,prl.requisition_line_id
,prl.item_id
,prl.unit_price
,prl.line_num
--,prl.vendor_id
--,prl.vendor_site_id
,prl.attribute14
,prl.unit_meas_lookup_code
,prl.destination_organization_id
,prl.org_id
,prl.deliver_to_location_id
,prh.preparer_id
,prh.authorization_status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_header_id=p_requistion_header_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id ;
BEGIN
DBMS_OUTPUT.PUT_LINE('CREATE PO PROCEDURE STARTS');
fnd_file.put_line (fnd_file.log,'PO CREATE Procedure starts here.');
fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);
mo_global.SET_POLICY_CONTEXT('S',204);
for i in po_interface_headers_rec
loop
fnd_file.put_line(fnd_file.log,'requistion_header_id = '||i.requisition_header_id);
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
org_id,
document_type_code,
document_subtype,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location,
bill_to_location,--,
group_code,
style_id
)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
apps.po_headers_interface_s.currval,
'PO',
'PENDING',
'NEW', -- Indicates this is a new document
i.org_id, -- Org id for operating unit Vision Operations
'PO', -- Indicates a standard PO is being imported
'STANDARD',
'USD', -- The currency to be used in the PO
i.preparer_id, -- The ID of the buyer
i.vendor_id, -- Supplier name
i.vendor_site_id, -- Supplier Site
i.ship_to_location, -- Ship to location name
i.bill_to_location,--, -- Bill to location name
'DEFAULT',--'TestPO'
1
);
FOR j IN po_interface_lines_rec (i.requisition_header_id)
LOOP
INSERT INTO po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
--shipment_num,
line_type,
item_id,--item,
unit_of_measure,
quantity,
unit_price,
--ship_to_organization_code,
--ship_to_location,
requisition_line_id
)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
j.line_num,
--1,
'Goods',
j.item_id, -- Item to imported on the PO line
j.unit_meas_lookup_code, -- Unit of Measure
j.quantity,
j.unit_price, -- Line price in specified currency
j.requisition_line_id--'V1', -- Inventory Organization which will receive the shipment
--'V1- New York City'
);
dbms_output.put_line('insertion done in line interface');
fnd_file.put_line(fnd_file.log,'insertion done in line interface');
END LOOP;
commit;
dbms_output.put_line('api_calling');
fnd_file.put_line(fnd_file.log,'api calling');
FND_MSG_PUB.Initialize;
dbms_output.put_line('po_headers_interface_s.currvalis'||po_headers_interface_s.currval);
PO_INTERFACE_S.create_documents (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => po_headers_interface_s.currval,
p_req_operating_unit_id => i.org_id,--–p_req_operating_unit_id,
p_purch_operating_unit_id =>i.org_id,--–p_purch_operating_unit_id,
x_document_id => l_document_id,
x_number_lines => l_num_lines_processed,
x_document_number => l_document_number, --— <DBI FPJ>
p_sourcing_k_doc_type => null,
p_conterms_exist_flag => null,
p_document_creation_method => 'AUTOCREATE',
p_orig_org_id => null
);
fnd_file.put_line(fnd_file.log,'api calling done');
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
fnd_file.put_line(fnd_file.log,'l_return_status='||l_return_status);
UPDATE xxspanv_po_req_preinteface a
SET po_num=l_document_number,
error_flag='P',
a.error_msg=null
WHERE a.suggested_vendor_name=i.suggested_vendor_name
AND a.suggested_vendor_site=i.suggested_vendor_site
AND a.batch_id=p_batch_id;
COMMIT;
ELSIF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
ROLLBACK;
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(p_msg_index => i,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_msg_index_out);
l_msg := l_msg||'.'|| l_msg_data;
END LOOP;
FND_MSG_PUB.DELETE_MSG();
UPDATE xxspanv_po_req_preinteface a
SET --po_num=l_document_number,
error_flag='E',
a.error_msg=L_MSG
WHERE a.suggested_vendor_name =i.suggested_vendor_name
AND a.suggested_vendor_site =i.suggested_vendor_site
AND a.batch_id=p_batch_id;
--AND a.item_name =i.item_name;
COMMIT;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error occure in procedure create_po'||SQLERRM);
dbms_output.put_line('exception occured'||sqlerrm);
END CREATE_PO;
end xxspanvi_req_po_load;
/
IS
PROCEDURE MAIN (errbuf OUT varchar2,
retcode OUT number,
p_batch_id number)
IS
CURSOR c_requistion_rec IS
SELECT *
FROM
xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id
and batch_id not in (select substr(attribute14,12) from po_requisition_lines_all);
l_error_flag varchar2 (1);
l_error_msg varchar2 (1000);
l_record_id number ;
l_item_id number ;
l_emp_id number ;
l_location_id number ;
l_organization_id number;
l_need_by_date date;
l_org_id number;
l_vendor_id number;
l_site_id number;
l_requestor_id number;
l_chanrge_account_id number;
--l_request_stat varchar2 (10);
BEGIN
BEGIN
SELECT NVL(MAX(record_id),0)
INTO l_record_id
FROM xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id;
UPDATE xxspanv_po_req_preinteface
SET record_id=l_record_id+ROWNUM;
COMMIT;
END;
fnd_file.put_line(fnd_file.log,'Validation started for stageging table');
FOR v_requistion_rec IN c_requistion_rec LOOP
----initializtion local varibale to null-----------
l_error_flag := null;
l_error_msg := null;
l_item_id :=null ;
l_emp_id :=null;
l_location_id :=null;
l_organization_id :=null;
l_need_by_date :=null;
l_org_id :=null;
l_vendor_id :=null;
l_site_id :=null;
l_requestor_id :=null;
l_chanrge_account_id :=null;
-----------VALIDATION STARTS HERE------------------
IF v_requistion_rec.INTERFACE_SOURCE_CODE IS NULL THEN
l_error_flag := 'E';
l_error_msg := 'INTERFACE_SOURCE_CODE Can not be Null';
END IF;
IF v_requistion_rec.requisition_type IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'REQUISITION_TYPE Can not be Null';
END IF;
IF v_requistion_rec.destination_type_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_TYPE_CODE Can not be Null';
END IF;
IF v_requistion_rec.item_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'ITEM_NAME Can not be Null';
ELSE
BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1)=UPPER(v_requistion_rec.item_name);
EXCEPTION WHEN others THEN
-- l_item_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'error occure while retreiving ITEM id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.quantity IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'QUANTITY Can not be Null';
END IF;
IF v_requistion_rec.preparer_full_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
ELSE
BEGIN
SELECT DISTINCT per.person_id
INTO l_emp_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.preparer_full_name )
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;
EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving emp_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.uom_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
END IF;
IF v_requistion_rec.destination_organization IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_ORGANIZATION Can not be Null';
ELSE
BEGIN
SELECT organization_id
INTO l_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE UPPER(ORGANIZATION_NAME)=UPPER(v_requistion_rec.DESTINATION_ORGANIZATION);
EXCEPTION WHEN OTHERS THEN
--l_organization_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Eroor occure while retreiving inventory org_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.deliver_to_location IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_LOCATION can not be null';
ELSE
BEGIN
SELECT location_id
INTO l_location_id
FROM HR_LOCATIONS
WHERE UPPER(location_code)=UPPER(v_requistion_rec.deliver_to_location);
EXCEPTION WHEN OTHERS THEN
--l_location_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving the deliver_to_location_id'||SQLERRM;
END ;
END IF;
IF v_requistion_rec.deliver_to_requestor IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_REQUESTOR can not be null';
ELSE
BEGIN
SELECT per.person_id
INTO l_requestor_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.deliver_to_requestor)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;
EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving requestor_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.charge_account IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'CHARGE_ACCOUNT can not be null';
ELSE
BEGIN
SELECT DISTINCT code_combination_id
INTO
l_chanrge_account_id
FROM gl_code_combinations_kfv a,
GL_SETS_OF_BOOKS gl
WHERE concatenated_segments=v_requistion_rec.charge_account
AND gl.chart_of_accounts_id=a.chart_of_accounts_id
AND gl.set_of_books_id=fnd_profile.value('gl_set_of_bks_id') ;
EXCEPTION WHEN OTHERS THEN
--l_chanrge_account_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the charge_account_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.need_by_date IS NULL THEN
l_need_by_date:= SYSDATE+1;
ELSE l_need_by_date:=v_requistion_rec.need_by_date;
END IF;
IF v_requistion_rec.ou_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'OU_NAME can not be null';
ELSE
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE UPPER(NAME)=UPPER(v_requistion_rec.ou_name);
EXCEPTION WHEN OTHERS THEN
--l_org_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the org_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.unit_price IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'UNIT_PRICE can not be null';
END IF;
IF v_requistion_rec.suggested_vendor_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_NAME can not be null';
ELSE
BEGIN
SELECT vendor_id
INTO
l_vendor_id
FROM AP_SUPPLIERS
WHERE UPPER(vendor_name)=UPPER(v_requistion_rec.suggested_vendor_name);
EXCEPTION WHEN OTHERS
THEN
--l_vendor_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the vendor_id'||SQLERRM;
END;
END IF;
IF v_requistion_rec.suggested_vendor_site IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_SITE can not be null';
ELSE
BEGIN
SELECT vendor_site_id
INTO l_site_id
FROM ap_supplier_sites
WHERE UPPER(vendor_site_code)=UPPER(v_requistion_rec.suggested_vendor_site)
AND vendor_id=l_vendor_id
AND org_id=l_org_id;
EXCEPTION WHEN OTHERS THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the site_id'||SQLERRM;
END;
END IF;
-------VALIDATION ENDS HERE------------------
------STRTING THE INSERTION IN REQUSITION INTERFACE -------------------------
IF l_error_flag IS NULL THEN
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(batch_id
,TRANSACTION_ID
,interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
--,category_id
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,charge_account_id
,need_by_date
,org_id
,unit_price
,autosource_flag
,line_attribute13
,line_attribute14
,suggested_vendor_id
,suggested_vendor_site_id
)
values
(v_requistion_rec.batch_id
,PO_REQUISITIONS_INTERFACE_S.nextval
,'FUTURE'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,l_item_id
,v_requistion_rec.item_description
,v_requistion_rec.quantity
,'INCOMPLETE'
,l_emp_id
,v_requistion_rec.uom_code
,l_organization_id
,v_requistion_rec.destination_subinventory
,l_location_id
,l_requestor_id
,l_chanrge_account_id
,l_need_by_date
,l_org_id
,v_requistion_rec.unit_price
,NULL
,v_requistion_rec.record_id
,'REQ_WRAPPER'||p_batch_id
,l_vendor_id
,l_site_id
);
ELSE
UPDATE xxspanv_po_req_preinteface
SET ERROR_FLAG=l_error_flag,
ERROR_MSG =l_error_msg
WHERE batch_id=v_requistion_rec.batch_id
AND record_id =v_requistion_rec.record_id;
END IF;
COMMIT;
END LOOP;
SUBMIT_REQUEST (P_BATCH_ID);
END MAIN;
PROCEDURE SUBMIT_REQUEST (P_BATCH_ID IN NUMBER --,
--STATUS OUT VARCHAR2
)
IS
l_request_id number;
l_max_wait number;
l_phase varchar2(10);
l_status varchar2(10);
l_dev_status varchar2(10);
l_message varchar2(100);
l_interval number;
l_dev_phase varchar2(10);
rphase varchar2(10);
rstatus varchar2(10);
dphase varchar2(10);
dstatus varchar2(10);
message varchar2(100);
call_status boolean ;
wait_status boolean ;
BEGIN
fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);
l_request_id:= fnd_request.submit_request('PO'
,'REQIMPORT'
,NULL
,NULL
,FALSE
,NULL---arg1
,p_batch_id
,'VENDOR'
,NULL
,NULL
,'Y'
);
COMMIT;
wait_status:=fnd_concurrent.wait_for_request (l_request_id,
60 ,
0,
l_phase ,
l_status ,
l_dev_phase,
l_dev_status,
L_message);
-- call_status :=fnd_concurrent.get_request_status(l_request_id, '', '',
-- rphase,rstatus,dphase,dstatus, message);
fnd_file.put_line(fnd_file.log,'dphase = '||l_dev_phase||'and '||'dstatus ='||l_dev_status) ;
IF UPPER(l_dev_phase)='COMPLETE' AND UPPER(l_dev_status)= 'NORMAL' THEN
dbms_output.put_line ('calling the procuedure to create po');----calling the po_creation procedure
fnd_file.put_line(fnd_file.log,'calling the procuedure to create po');
CREATE_PO (P_BATCH_ID);
END IF;
exception when others
then
fnd_file.put_line(fnd_file.log,'Error occure in procedure submit_request'||SQLERRM);
END SUBMIT_REQUEST;
----to create po using aoutocreate need to insert all approved requisions into po interfaces tables and then calling the api--
PROCEDURE CREATE_PO (P_BATCH_ID IN number)
IS
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(5000);
l_num_lines_processed number;
l_document_number po_headers_all.segment1%TYPE;
l_document_id number;
l_msg varchar2(5000);
l_msg_index_out number;
CURSOR po_interface_headers_rec IS
SELECT distinct prl.requisition_header_id
,prl.vendor_id
,prl.vendor_site_id
,prl.attribute14
--,prl.unit_meas_lookup_code
,prl.destination_organization_id
--,prl.org_id
,prh.org_id
--,prl.deliver_to_location_id
,prh.preparer_id
--,prh.authorization_status
,xprp.ship_to_location
,xprp.bill_to_location
--,xprp.item_name
,xprp.suggested_vendor_name
,xprp.suggested_vendor_site
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
xxspanv_po_req_preinteface xprp
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND to_char(xprp.batch_id)=substr(prl.attribute14,12)
--AND to_char(xprp.record_id)=(prl.ATTRIBUTE13)
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id;
CURSOR po_interface_lines_rec (p_requistion_header_id number)
IS
SELECT prl.requisition_header_id
,prl.quantity
,prl.requisition_line_id
,prl.item_id
,prl.unit_price
,prl.line_num
--,prl.vendor_id
--,prl.vendor_site_id
,prl.attribute14
,prl.unit_meas_lookup_code
,prl.destination_organization_id
,prl.org_id
,prl.deliver_to_location_id
,prh.preparer_id
,prh.authorization_status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_header_id=p_requistion_header_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id ;
BEGIN
DBMS_OUTPUT.PUT_LINE('CREATE PO PROCEDURE STARTS');
fnd_file.put_line (fnd_file.log,'PO CREATE Procedure starts here.');
fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);
mo_global.SET_POLICY_CONTEXT('S',204);
for i in po_interface_headers_rec
loop
fnd_file.put_line(fnd_file.log,'requistion_header_id = '||i.requisition_header_id);
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
org_id,
document_type_code,
document_subtype,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location,
bill_to_location,--,
group_code,
style_id
)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
apps.po_headers_interface_s.currval,
'PO',
'PENDING',
'NEW', -- Indicates this is a new document
i.org_id, -- Org id for operating unit Vision Operations
'PO', -- Indicates a standard PO is being imported
'STANDARD',
'USD', -- The currency to be used in the PO
i.preparer_id, -- The ID of the buyer
i.vendor_id, -- Supplier name
i.vendor_site_id, -- Supplier Site
i.ship_to_location, -- Ship to location name
i.bill_to_location,--, -- Bill to location name
'DEFAULT',--'TestPO'
1
);
FOR j IN po_interface_lines_rec (i.requisition_header_id)
LOOP
INSERT INTO po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
--shipment_num,
line_type,
item_id,--item,
unit_of_measure,
quantity,
unit_price,
--ship_to_organization_code,
--ship_to_location,
requisition_line_id
)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
j.line_num,
--1,
'Goods',
j.item_id, -- Item to imported on the PO line
j.unit_meas_lookup_code, -- Unit of Measure
j.quantity,
j.unit_price, -- Line price in specified currency
j.requisition_line_id--'V1', -- Inventory Organization which will receive the shipment
--'V1- New York City'
);
dbms_output.put_line('insertion done in line interface');
fnd_file.put_line(fnd_file.log,'insertion done in line interface');
END LOOP;
commit;
dbms_output.put_line('api_calling');
fnd_file.put_line(fnd_file.log,'api calling');
FND_MSG_PUB.Initialize;
dbms_output.put_line('po_headers_interface_s.currvalis'||po_headers_interface_s.currval);
PO_INTERFACE_S.create_documents (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => po_headers_interface_s.currval,
p_req_operating_unit_id => i.org_id,--–p_req_operating_unit_id,
p_purch_operating_unit_id =>i.org_id,--–p_purch_operating_unit_id,
x_document_id => l_document_id,
x_number_lines => l_num_lines_processed,
x_document_number => l_document_number, --— <DBI FPJ>
p_sourcing_k_doc_type => null,
p_conterms_exist_flag => null,
p_document_creation_method => 'AUTOCREATE',
p_orig_org_id => null
);
fnd_file.put_line(fnd_file.log,'api calling done');
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
fnd_file.put_line(fnd_file.log,'l_return_status='||l_return_status);
UPDATE xxspanv_po_req_preinteface a
SET po_num=l_document_number,
error_flag='P',
a.error_msg=null
WHERE a.suggested_vendor_name=i.suggested_vendor_name
AND a.suggested_vendor_site=i.suggested_vendor_site
AND a.batch_id=p_batch_id;
COMMIT;
ELSIF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
ROLLBACK;
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(p_msg_index => i,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_msg_index_out);
l_msg := l_msg||'.'|| l_msg_data;
END LOOP;
FND_MSG_PUB.DELETE_MSG();
UPDATE xxspanv_po_req_preinteface a
SET --po_num=l_document_number,
error_flag='E',
a.error_msg=L_MSG
WHERE a.suggested_vendor_name =i.suggested_vendor_name
AND a.suggested_vendor_site =i.suggested_vendor_site
AND a.batch_id=p_batch_id;
--AND a.item_name =i.item_name;
COMMIT;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error occure in procedure create_po'||SQLERRM);
dbms_output.put_line('exception occured'||sqlerrm);
END CREATE_PO;
end xxspanvi_req_po_load;
/
xxspanv_po_req_preinteface is not a standard table and you don't give the create script :(
ReplyDeleteYes. Thats true. Its actually staging table where data gets validated And validayed data is paases to api.
ReplyDeleteWhile importing the request the line number order changes.
DeleteThe uploaded data line order is different from imported order.How to import the order in uploaded order.
Yes. Thats true. Its actually staging table where data gets validated And validayed data is paases to api.
ReplyDeleteduring the validation process any data is not passing validation updated back to the staging with respective error
ReplyDeleteduring the validation process any data is not passing validation updated back to the staging with respective error
ReplyDeleteCan you give me the script of this table?
ReplyDeleteHi
DeleteSorry 4 delay in reply.
Send me ur email id . I will send it u on email.
Very helpful stuff. thanks
ReplyDeleteCan you give me the script of this table? mail id : pkrhyd@gmail.com
ReplyDeleteHey can u plz send the script to this mail id : harish12.desu@gmail.com
DeleteHi Prasanta
ReplyDeleteI will see the script and send it to u
hello bharat can u send the script to this mail id : lagipercobaan1@gmail.com
Deletesir can send script for table xxspanv_po_req_preinteface to this mail id : lagipercobaan1@gmail.com thank you
DeleteHi Prasanta
ReplyDeleteI will see the script and send it to u
Hello Bharat,
ReplyDeletePlease can you send script of the staging table? email id: cutemax@rediffmail.com
send script to this mail id : harish12.desu@gmail.com
DeleteHey can you please send the script(including staging table) to this mail id: harish12.desu@gmail.com
ReplyDeleteASAP....
hi, can anyone pass me the create table script for xxspanv_po_req_preinteface asap ..
ReplyDeleteemail id - raamjeece@gmail.com
Please send me the script including staging table to udayapatilkumar@gmail.com
ReplyDeletePlease send me Process to Import Requisition automation on Oracle R12 - mail id - mandarjejurikar@gmail.com
ReplyDeleteHi,
ReplyDeleteWhen use this code i am getting the following error
" User-Defined Exception at location 020 in Package PO_INTERFACE_S Procedure create_documents"
could you please advise on this error...
plz send script including staging table to sumit93walse@gmail.com
ReplyDeletekindly send me staging table script at ramzan308@gmail.com
ReplyDeleteHi Bharat,
ReplyDeletePlease can you send me the Staging table script to my email id,
aadi.reddy001@gmail.com
Regards,
Aadi.
plz send script including staging table shanmukha.koppaka525@gmail.com
ReplyDeleteHi Mr. Patil,
ReplyDeleteI am working on this exact requirement.
Could you please send me the staging table script to my email.
t17kirtiwardhan@gmail.com
Thanks.
Thank You So Much Sir It is so much helpful...!!!
ReplyDeleteThank You Sir it is understandable and useful script.
ReplyDeletePankaj
Please give me the package header and staging table details at
ReplyDeletebiswasnd7@gmail.com