CREATE OR REPLACE PACKAGE FUJ_POREQ_PO_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_ABSENCE_ATTENDANCE_ID NUMBER) ;
procedure FUJ_PO_CREATION(
Errbuff out varchar2,
retcode out varchar2,
P_REQ_HEAD_ID IN number);
END;
CREATE OR REPLACE PACKAGE BODY FUJ_POREQ_PO_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_ABSENCE_ATTENDANCE_ID NUMBER)
IS
CURSOR C1 IS
SELECT PAA.PERSON_ID,ABSENCE_ATTENDANCE_ID,DATE_START, DATE_END, ABSENCE_DAYS
,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME, PAPF.PER_INFORMATION18
FROM
PER_ABSENCE_ATTENDANCES PAA,
PER_ALL_PEOPLE_F PAPF
WHERE
--PAA.PERSON_ID = 26876
ABSENCE_ATTENDANCE_ID = 3707187
AND PAPF.PERSON_ID = PAA.PERSON_ID
AND DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
BEGIN
FOR I IN C1 LOOP
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
ORG_ID,
DESTINATION_TYPE_CODE,
AUTHORIZATION_STATUS,
PREPARER_ID,
--PREPARER_NAME,
CHARGE_ACCOUNT_ID,
SOURCE_TYPE_CODE,
UNIT_OF_MEASURE,
LINE_TYPE_ID,
CATEGORY_ID,
UNIT_PRICE,
QUANTITY,
DESTINATION_ORGANIZATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_REQUESTOR_ID,
HEADER_DESCRIPTION,
ITEM_DESCRIPTION,
SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE_ID,
--ACCRUAL_ACCOUNT_ID ,
--VARIANCE_ACCOUNT_ID,
--BUDGET_ACCOUNT_ID,
HEADER_ATTRIBUTE1,
HEADER_ATTRIBUTE2,
HEADER_ATTRIBUTE3,
HEADER_ATTRIBUTE4,
GL_DATE,
REQUISITION_HEADER_ID
)
VALUES ('IMPORT_EXP', --Interface Source
102, --Operating Unit
'EXPENSE', --Destination Type
'INCOMPLETE', -- 'APPROVED',-- --Status
26876,--6439, --This comes from per_people_f.person_id
-- '???? ??? ????? ????? ?????????',
106847, --Code Combination ID
'VENDOR', --Source Type
'Quantity', --UOM ok??yes
1, --Line Type of Goods
1123, --MISC.MISC Category
100, --Price
1, --quantity
108,--102 --Represents Vision Operations Inv Org.
142,--162, --Represents V1-New York City
26876,--6439 , --This is the Deliver to Requestor
I.EMPLOYEE_NUMBER, --One Time Header Description
I.EMPLOYEE_NUMBER, --One Time Item Description
751527,
768622,
--17979,--Accrual
--106847,--Varianlce
--106847, --Budget
'Y',--Header. ATT1
I.PER_INFORMATION18,--Header 2
'New Delhi',--Header 3
I.PERSON_ID,--Header 4
TO_DATE('01-JAN-2017'),
PO_REQUISITION_HEADERS_S.NEXTVAL
);
COMMIT;
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
procedure FUJ_PO_CREATION(
Errbuff out varchar2,retcode out varchar2,
P_REQ_HEAD_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
, 142 ship_to_location
, 142 bill_to_location
--,xprp.item_name
,SUGGESTED_VENDOR_NAME--,xprp.suggested_vendor_name 751527
,SUGGESTED_VENDOR_LOCATION--,xprp.suggested_vendor_site 768622
,prh.description
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 prh.REQUISITION_HEADER_ID = P_REQ_HEAD_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 prh.REQUISITION_HEADER_ID = P_REQ_HEAD_ID
--AND prl.attribute14='REQ_WRAPPER'||p_batch_id
;
L_USER_ID NUMBER:=1224;--2605;--fnd_profile.value('USER_ID');
L_RESP_ID NUMBER:= 50597;--fnd_profile.value('RESP_ID');
L_RESP_APPL_ID NUMBER:= 201;--fnd_profile.value('RESP_APPL_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(L_USER_ID,L_RESP_ID,L_RESP_APPL_ID ,
null,
null
);
mo_global.SET_POLICY_CONTEXT('S',102); --change this
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_id,
bill_to_location_id,--,
group_code,
style_id
,comments
,GL_ENCUMBERED_DATE
)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
apps.po_headers_interface_s.currval,
'PO',
'APPROVED',--'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',
'AED', -- 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
142,--i.ship_to_location, -- Ship to location name
142,--i.bill_to_location,--, -- Bill to location name
'DEFAULT',--'TestPO'
1,
'Test from api ',--i.description
to_date('01-jan-2017')
);
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
);
end loop;
END;
END;
IS
PROCEDURE POREQ_CREATION( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_ABSENCE_ATTENDANCE_ID NUMBER) ;
procedure FUJ_PO_CREATION(
Errbuff out varchar2,
retcode out varchar2,
P_REQ_HEAD_ID IN number);
END;
CREATE OR REPLACE PACKAGE BODY FUJ_POREQ_PO_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_ABSENCE_ATTENDANCE_ID NUMBER)
IS
CURSOR C1 IS
SELECT PAA.PERSON_ID,ABSENCE_ATTENDANCE_ID,DATE_START, DATE_END, ABSENCE_DAYS
,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME, PAPF.PER_INFORMATION18
FROM
PER_ABSENCE_ATTENDANCES PAA,
PER_ALL_PEOPLE_F PAPF
WHERE
--PAA.PERSON_ID = 26876
ABSENCE_ATTENDANCE_ID = 3707187
AND PAPF.PERSON_ID = PAA.PERSON_ID
AND DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
BEGIN
FOR I IN C1 LOOP
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
ORG_ID,
DESTINATION_TYPE_CODE,
AUTHORIZATION_STATUS,
PREPARER_ID,
--PREPARER_NAME,
CHARGE_ACCOUNT_ID,
SOURCE_TYPE_CODE,
UNIT_OF_MEASURE,
LINE_TYPE_ID,
CATEGORY_ID,
UNIT_PRICE,
QUANTITY,
DESTINATION_ORGANIZATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_REQUESTOR_ID,
HEADER_DESCRIPTION,
ITEM_DESCRIPTION,
SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE_ID,
--ACCRUAL_ACCOUNT_ID ,
--VARIANCE_ACCOUNT_ID,
--BUDGET_ACCOUNT_ID,
HEADER_ATTRIBUTE1,
HEADER_ATTRIBUTE2,
HEADER_ATTRIBUTE3,
HEADER_ATTRIBUTE4,
GL_DATE,
REQUISITION_HEADER_ID
)
VALUES ('IMPORT_EXP', --Interface Source
102, --Operating Unit
'EXPENSE', --Destination Type
'INCOMPLETE', -- 'APPROVED',-- --Status
26876,--6439, --This comes from per_people_f.person_id
-- '???? ??? ????? ????? ?????????',
106847, --Code Combination ID
'VENDOR', --Source Type
'Quantity', --UOM ok??yes
1, --Line Type of Goods
1123, --MISC.MISC Category
100, --Price
1, --quantity
108,--102 --Represents Vision Operations Inv Org.
142,--162, --Represents V1-New York City
26876,--6439 , --This is the Deliver to Requestor
I.EMPLOYEE_NUMBER, --One Time Header Description
I.EMPLOYEE_NUMBER, --One Time Item Description
751527,
768622,
--17979,--Accrual
--106847,--Varianlce
--106847, --Budget
'Y',--Header. ATT1
I.PER_INFORMATION18,--Header 2
'New Delhi',--Header 3
I.PERSON_ID,--Header 4
TO_DATE('01-JAN-2017'),
PO_REQUISITION_HEADERS_S.NEXTVAL
);
COMMIT;
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
procedure FUJ_PO_CREATION(
Errbuff out varchar2,retcode out varchar2,
P_REQ_HEAD_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
, 142 ship_to_location
, 142 bill_to_location
--,xprp.item_name
,SUGGESTED_VENDOR_NAME--,xprp.suggested_vendor_name 751527
,SUGGESTED_VENDOR_LOCATION--,xprp.suggested_vendor_site 768622
,prh.description
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 prh.REQUISITION_HEADER_ID = P_REQ_HEAD_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 prh.REQUISITION_HEADER_ID = P_REQ_HEAD_ID
--AND prl.attribute14='REQ_WRAPPER'||p_batch_id
;
L_USER_ID NUMBER:=1224;--2605;--fnd_profile.value('USER_ID');
L_RESP_ID NUMBER:= 50597;--fnd_profile.value('RESP_ID');
L_RESP_APPL_ID NUMBER:= 201;--fnd_profile.value('RESP_APPL_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(L_USER_ID,L_RESP_ID,L_RESP_APPL_ID ,
null,
null
);
mo_global.SET_POLICY_CONTEXT('S',102); --change this
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_id,
bill_to_location_id,--,
group_code,
style_id
,comments
,GL_ENCUMBERED_DATE
)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
apps.po_headers_interface_s.currval,
'PO',
'APPROVED',--'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',
'AED', -- 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
142,--i.ship_to_location, -- Ship to location name
142,--i.bill_to_location,--, -- Bill to location name
'DEFAULT',--'TestPO'
1,
'Test from api ',--i.description
to_date('01-jan-2017')
);
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
);
end loop;
END;
END;
if the po is created as INCOMPLETE status, then run the below code.
https://mogalafzal.blogspot.com/2023/04/apps-r12-po-approve-from-backend-from.html
No comments:
Post a Comment