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;
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;
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;
/
thanks alot,
ReplyDeletecan you send table structuer ?xxspanv_po_req_preinteface
and what is l_record_id refer to ?
mohammed.irshaid@gmail.com