Procedure to Create the PO:
PROCEDURE create_po ( errbuf
OUT varchar2
, retcode OUT number
, p_req_id IN
number )
IS
---Declaring a cursor for header
CURSOR c_header_details
IS
SELECT
bjf_header_id
, staff_number
, designation
, department
, email_address
, cost_center
, request_type
, service_type
, need_by_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, requirement_summary
, justification
, hr_ccid
, req_account_id
, process_flag
, org_id
, requester_name
, preparer_name
, preparer_id
, request_status
, currency_code
, supplier_name
, vendor_id
, vendor_site_id
, error_message
, request_number
, requester_id
, request_id
, request_date
, request_reason
, replacement_code
, other_reason
FROM
xxey_bjf_request_header_t
WHERE
bjf_header_id = p_req_id;
--Decalring a cursor for line
CURSOR c_line_details
IS
SELECT
bjf_line_id
, bjf_header_id
, line_num
, user_staff_num
, request_desc
, concatenated_segments
, unit_price
, quantity
, line_amount
, uom_code
, category_id
, category_name
, item_type
, item_id
, org_id
, destination_type_code
,
destination_organization_code
, deliver_to_location_code
, deliver_to_requestor
, source_organization_code
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, item_name
, request_id
, process_flag
, error_message
FROM
xxey_bjf_request_lines_t
WHERE
bjf_header_id = p_req_id;
--Dcalring a cursor to get
active vendor
CURSOR c_vendor (
p_vendor_id IN number
, p_vendor_site_id IN number
, p_org_id IN number
)
IS
SELECT
pv.vendor_id, pvsa.vendor_site_id
FROM
po_vendors pv, po_vendor_sites_all pvsa
WHERE
pv.vendor_id = pvsa.vendor_id
AND pvsa.org_id = p_org_id
AND pv.enabled_flag = 'Y'
AND pv.vendor_id = p_vendor_id
AND pvsa.inactive_date IS NULL;
--Decalring a cursor to check vendor
existance
CURSOR c_agent ( p_agent_name IN varchar2 )
IS
SELECT
agent_id
FROM
po_agents_v
WHERE
agent_name = p_agent_name;
---Decalring a cursor to get
currency code
CURSOR c_curr (
p_org_id IN number
)
IS
SELECT
currency_code
FROM
gl_sets_of_books gsb, hr_operating_units hou
WHERE
gsb.set_of_books_id = hou.set_of_books_id
AND hou.organization_id = p_org_id;
---variable declaration---
ln_vendor_id number;
ln_vendor_site_id number;
ln_header_id number;
ln_line_id number;
ln_dist_id number;
lc_rec_status varchar2 ( 1 );
ln_agent_id number;
lc_base_curr varchar2 ( 15 );
lc_rate_type varchar2 ( 20 );
lc_rate_date date;
lc_err_msg varchar2 ( 240 );
le_header_vald exception;
BEGIN
FOR lcu_header_details IN c_header_details
LOOP
BEGIN
OPEN c_vendor( lcu_header_details.vendor_id
, lcu_header_details.vendor_site_id
, lcu_header_details.org_id);
FETCH c_vendor INTO ln_vendor_id, ln_vendor_site_id;
CLOSE c_vendor;
IF ln_vendor_id IS NULL OR ln_vendor_site_id IS NULL
THEN
-- ln_vendor_id := 47;
-- ln_vendor_site_id :=
25867;
lc_rec_status := 'E';
lc_err_msg := 'Vendor does not exists';
RAISE le_header_vald;
END IF;
OPEN c_agent (fnd_profile.VALUE ( 'XXEY_BJF_PO_AGENT' ));
FETCH c_agent INTO ln_agent_id;
CLOSE c_agent;
IF ln_agent_id IS NULL
THEN
lc_rec_status := 'E';
lc_err_msg
:= 'Buyer does not exists';
RAISE le_header_vald;
END IF;
OPEN c_curr(lcu_header_details.org_id);
FETCH c_curr INTO lc_base_curr;
CLOSE c_curr;
IF lc_base_curr = lcu_header_details.currency_code
THEN
lc_rate_type := NULL;
lc_rate_date := NULL;
ELSE
lc_rate_type := 'Corporate';
lc_rate_date := lcu_header_details.request_date;
END IF;
--PO Header Creation
SELECT
po_headers_interface_s.NEXTVAL
INTO ln_header_id
FROM DUAL;
INSERT INTO po_headers_interface ( interface_header_id
, interface_source_code
, action
, org_id
, document_type_code
, document_num
, currency_code
, rate_type
, rate_date
, agent_id
, vendor_id
, vendor_site_id
-- , ship_to_location
, approval_status
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, attribute11 )
VALUES ( ln_header_id
, 'BJF'
, 'ORIGINAL'
, lcu_header_details.org_id
, 'STANDARD'
, --'EY-' ||
lcu_header_details.request_number
, lcu_header_details.currency_code
, lc_rate_type
, lc_rate_date
, ln_agent_id
, ln_vendor_id--lcu_header_details.vendor_id
, ln_vendor_site_id --lcu_header_details.vendor_site_id
-- ,
lcu_header_details.deliver_to_location_code
, 'APPROVED'
, SYSDATE
, gn_user_id
, gn_login_id
, SYSDATE
, gn_user_id
, lcu_header_details.bjf_header_id );
FOR lcu_lines IN c_line_details
LOOP
BEGIN
--PO Line Creation
SELECT po_lines_interface_s.NEXTVAL
INTO ln_line_id
FROM DUAL;
INSERT INTO po_lines_interface ( interface_line_id
, interface_header_id
, line_num
, shipment_num
, line_type
, item_description
, category_id
, uom_code
-- unit_of_measure
, quantity
, unit_price
, ship_to_location
, need_by_date
, promised_date
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by )
VALUES ( ln_line_id
, ln_header_id
, lcu_lines.line_num
, lcu_lines.line_num
, 'Services'
, lcu_lines.request_desc
, lcu_lines.category_id
, 'EA'--lcu_lines.uom_code
, lcu_lines.quantity
, lcu_lines.unit_price
, lcu_lines.deliver_to_location_code
, SYSDATE +10 --(
lcu_header_details.request_date ) + 1
, lcu_header_details.request_date + 1
, SYSDATE
, gn_user_id
, gn_login_id
, SYSDATE
, gn_user_id );
SELECT po_distributions_interface_s.NEXTVAL
INTO ln_dist_id
FROM DUAL;
--PO Distribution Creation
INSERT INTO po_distributions_interface (
interface_header_id
, interface_line_id
, interface_distribution_id
, deliver_to_location
, quantity_ordered
, charge_account_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
)
VALUES ( ln_header_id
, ln_line_id
, ln_dist_id
, lcu_lines.deliver_to_location_code
, lcu_lines.quantity
, lcu_header_details.req_account_id
, SYSDATE
, gn_user_id
, gn_login_id
, SYSDATE
, gn_user_id );
EXCEPTION
WHEN OTHERS
THEN
lc_err_msg := SUBSTR ( SQLERRM, 1, 240 );
UPDATE xxey_bjf_request_lines_t
SET process_flag = 'E', error_message = lc_err_msg
WHERE bjf_line_id = lcu_lines.bjf_line_id;
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN le_header_vald
THEN
UPDATE xxey_bjf_request_header_t
SET process_flag = 'E', error_message = lc_err_msg
WHERE bjf_header_id = lcu_header_details.bjf_header_id;
COMMIT;
WHEN OTHERS
THEN
lc_err_msg := SQLERRM;
UPDATE xxey_bjf_request_header_t
SET process_flag = 'E', error_message = lc_err_msg
WHERE bjf_header_id = lcu_header_details.bjf_header_id;
COMMIT;
END;
END LOOP;
--Calling Submit Import
program
submit_import ( p_req_id, lc_err_msg );
EXCEPTION
WHEN OTHERS
THEN
NULL;
END create_po;
Submitting Import Program Using PLSQL:
PROCEDURE submit_import ( p_request_id IN number, p_error_msg OUT varchar2 )
IS
ln_requested_by number;
ln_responsibility_id number;
ln_application_id number;
ln_req_id number;
ln_wait_interval number := 10;
ln_max_wait number := 1000;
lc_phase varchar2 ( 100 );
lc_status varchar2 ( 10 );
lc_dev_phase varchar2 ( 15 );
lc_dev_status varchar2 ( 15 );
lc_req_msg varchar2 ( 240 );
lb_get_req_status boolean;
lc_po_num varchar2 ( 20 );
ln_request_id number;
CURSOR c_get_req_update (
p_req_id IN number
)
IS
SELECT
segment1
FROM
po_headers_all
WHERE
attribute11 IS NOT NULL
AND attribute11 = TO_CHAR ( p_req_id )
AND interface_source_code = 'BJF';
BEGIN
--apps initialization----
fnd_global.apps_initialize ( 20054, 50280, 201 );
--calling the standard concurrent program
for po
ln_req_id :=
fnd_request.submit_request ( 'PO'
, 'POXPOPDOI'
, 'Import Standard Purchase
Orders'
, SYSDATE
, FALSE
, NULL
, 'STANDARD'
, NULL
, 'N'
, NULL
, 'APPROVED'
, NULL
, NULL
, NULL
, NULL );
fnd_file.put_line ( fnd_file.LOG, 'REQUEST ID:' || ln_req_id );
COMMIT;
IF ( ln_req_id > 0 )
THEN
fnd_file.put_line (
fnd_file.LOG
, 'Import Standard Purchase
Orders Program Successfully Submited'
);
lb_get_req_status :=
fnd_concurrent.wait_for_request ( ln_req_id
, ln_wait_interval
, ln_max_wait
, lc_phase
, lc_status
, lc_dev_phase
, lc_dev_status
, lc_req_msg );
IF UPPER ( lc_dev_phase ) = 'COMPLETE' AND UPPER ( lc_dev_status ) = 'NORMAL'
THEN
OPEN c_get_req_update(p_request_id);
FETCH c_get_req_update INTO lc_po_num;
CLOSE c_get_req_update;
IF lc_po_num IS NOT NULL
THEN
---update the staging table
with the RequisitionNumber and Rquest_id----
UPDATE xxey_bjf_request_header_t
SET requisition_number = lc_po_num
, request_id = gn_req_id
, process_flag = 'S'
, request_status = 'Pending IT Service Desk'
, last_update_date = SYSDATE
, last_updated_by = gn_user_id
, last_update_login = gn_user_id
WHERE bjf_header_id = p_request_id;
COMMIT;
ELSE
UPDATE xxey_bjf_request_header_t
SET process_flag = 'E'
, error_message = 'Error in PO Creation'
, request_id = gn_req_id
, last_update_date = SYSDATE
, last_updated_by = gn_user_id
, last_update_login = gn_user_id
WHERE bjf_header_id = p_request_id;
COMMIT;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG
, 'Error in Calling Standard
Program --> '
|| SQLERRM
);
END;
No comments:
Post a Comment