CREATE OR REPLACE PACKAGE APPS.kfsh_CanCEL_PO_PKG
/**
* Created by-
*Date:-03-Dec-2014
*Version-1.0
* Public Procedure: cancel_po
* Requires: API message list has been initialized if p_init_msg_list is false.
* Modifies: All columns related to the control action, and who columns. The API
* message list.
* Effects: Performs the control action p_action on the specified document.
* Currently, only the 'CANCEL' action is supported. If the control action was
* successful, the document will be updated at the specified entity level.
* Derives any ID if the ID is NULL, but the matching number is passed in. If
* both the ID and number are passed in, the ID is used. Executes at shipment
* level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
* at line level if only the final doc_id and line_id are not NULL. Executes
* at header level if only the final doc_id is not NULL. The document will be
* printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
* changes will be committed upon success if p_commit is FND_API.G_TRUE.
* Appends to API message list on error, and leaves the document unchanged.
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
* FND_API.G_RET_STS_ERROR if control action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
AS
PROCEDURE cancel_po (errbuff OUT VARCHAR2,
retcode OUT NUMBER,
p_po_num VARCHAR2,
p_line_num NUMBER,
p_ship_num NUMBER);
END kfsh_CanCEL_PO_PKG;
/
/**
* Created by-
*Date:-03-Dec-2014
*Version-1.0
* Public Procedure: cancel_po
* Requires: API message list has been initialized if p_init_msg_list is false.
* Modifies: All columns related to the control action, and who columns. The API
* message list.
* Effects: Performs the control action p_action on the specified document.
* Currently, only the 'CANCEL' action is supported. If the control action was
* successful, the document will be updated at the specified entity level.
* Derives any ID if the ID is NULL, but the matching number is passed in. If
* both the ID and number are passed in, the ID is used. Executes at shipment
* level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
* at line level if only the final doc_id and line_id are not NULL. Executes
* at header level if only the final doc_id is not NULL. The document will be
* printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
* changes will be committed upon success if p_commit is FND_API.G_TRUE.
* Appends to API message list on error, and leaves the document unchanged.
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
* FND_API.G_RET_STS_ERROR if control action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
AS
PROCEDURE cancel_po (errbuff OUT VARCHAR2,
retcode OUT NUMBER,
p_po_num VARCHAR2,
p_line_num NUMBER,
p_ship_num NUMBER);
END kfsh_CanCEL_PO_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.KFSH_CANCEL_PO_PKG
/**
* Created by-
*Date:-03-Dec-2014
*Version-1.0
* Public Procedure: cancel_po
* Requires: API message list has been initialized if p_init_msg_list is false.
* Modifies: All columns related to the control action, and who columns. The API
* message list.
* Effects: Performs the control action p_action on the specified document.
* Currently, only the 'CANCEL' action is supported. If the control action was
* successful, the document will be updated at the specified entity level.
* Derives any ID if the ID is NULL, but the matching number is passed in. If
* both the ID and number are passed in, the ID is used. Executes at shipment
* level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
* at line level if only the final doc_id and line_id are not NULL. Executes
* at header level if only the final doc_id is not NULL. The document will be
* printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
* changes will be committed upon success if p_commit is FND_API.G_TRUE.
* Appends to API message list on error, and leaves the document unchanged.
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
* FND_API.G_RET_STS_ERROR if control action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
AS
PROCEDURE CANCEL_PO (errbuff OUT VARCHAR2,
retcode OUT NUMBER,
p_po_num VARCHAR2,
p_line_num NUMBER,
p_ship_num NUMBER)
IS
v_return_status VARCHAR2 (10);
v_msg_data VARCHAR2 (1000);
v_po_header_id NUMBER;
v_doc_subtype VARCHAR2 (10) := 'STANDARD';
v_doc_type VARCHAR2 (10) := 'PO';
v_org_id NUMBER := fnd_profile.VALUE ('Org_id');
v_action VARCHAR2 (10) := 'CANCEL';
v_action_date DATE := SYSDATE;
v_line_num NUMBER;
v_ship_num NUMBER;
l_user_id NUMBER := fnd_profile.VALUE ('user_id');
l_resp_id NUMBER := 50127;
l_appl_id NUMBER := 201;
v_po_c_flag varchar2(10);
v_line_po_c_flag varchar2(10);
v_ship_po_c_flag varchar2(10);
v_line_loc_id number;
v_user_excp EXCEPTION;
v_po_cc EXCEPTION;
v_po_line_cc EXCEPTION;
v_po_ship_cc EXCEPTION;
BEGIN
fnd_file.
put_line (
fnd_file.output,
'-----------------------------------Procedure to cancel Po Started-----------------------------------------');
/* Check whether details are passed to Program*/
IF (p_po_num IS NULL AND p_line_num IS NULL AND p_ship_num IS NULL)
THEN
RAISE v_user_excp;
END IF;
/*Validate the Po Number*/
BEGIN
SELECT po_header_id,Cancel_flag
INTO v_po_header_id,v_po_c_flag
FROM apps.po_headers_all
WHERE segment1 = p_po_num
AND type_lookup_code IN ('STANDARD', 'PLANNED')
AND org_id = v_org_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (fnd_file.output,
'---------PO Number is Invalid---------' || p_po_num);
RAISE v_user_excp;
END;
/*Validate the Po line Number*/
IF p_line_num IS NOT NULL
THEN
BEGIN
SELECT po_line_id,cancel_flag
INTO v_line_num,v_line_po_c_flag
FROM apps.po_lines_all
WHERE po_header_id = v_po_header_id AND line_num = p_line_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (
fnd_file.output,
'po Line '
|| p_line_num
|| 'does not exists for po '
|| p_po_num);
RAISE v_user_excp;
END;
END IF;
/*Validate the Po shipment Number*/
IF p_ship_num IS NOT NULL
THEN
BEGIN
SELECT SHIPMENT_NUM,cancel_flag,LINE_LOCATION_ID
INTO v_ship_num,v_ship_po_c_flag,v_line_loc_id
FROM apps.po_line_locations_all
WHERE po_header_id = v_po_header_id AND po_line_id = v_line_num and shipment_num=p_ship_num
and SHIPMENT_TYPE <>'SCHEDULED';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (
fnd_file.output,
'Shipment::'
|| p_ship_num
|| 'does not exists for po Line '
|| p_line_num
|| 'and po '
|| p_po_num);
RAISE v_user_excp;
END;
END IF;
if v_po_c_flag ='Y' then
raise v_po_cc ;
end if;
if v_line_po_c_flag='Y' then
raise v_po_line_cc ;
end if;
if v_ship_po_c_flag='Y' then
raise v_po_ship_cc ;
end if;
/*All the validation passed*/
/* Cancel Po Header*/
IF (p_po_num IS NOT NULL AND p_line_num IS NULL AND p_ship_num IS NULL)
THEN
BEGIN
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
MO_GLOBAL.INIT ('PO');
mo_global.set_policy_context ('S', v_org_id);
DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
fnd_file.
put_line (fnd_file.output,
'Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.
CONTROL_DOCUMENT (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
p_doc_type => v_doc_type,
p_doc_subtype => v_doc_subtype,
p_doc_id => v_po_header_id,
p_doc_num => NULL,
p_release_id => NULL,
p_release_num => NULL,
p_doc_line_id => NULL,
p_doc_line_num => NULL,
p_doc_line_loc_id => NULL,
p_doc_shipment_num => NULL,
p_action => v_action,
p_action_date => v_action_date,
p_cancel_reason => 'OLD PURCHASE ORDER',
p_cancel_reqs_flag => 'N',
p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL,
p_org_id => v_org_id);
COMMIT;
DBMS_OUTPUT.
PUT_LINE ('The Return Status of the API : ' || v_return_status);
fnd_file.
put_line (fnd_file.output,
'The Return Status of the API : ' || v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.
put_line (fnd_file.output, 'PO Cancelled : ' || p_po_num);
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
ROLLBACK;
FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG
LOOP
v_msg_data :=
FND_MSG_PUB.GET (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.PUT_LINE (i || ') ' || v_msg_data);
fnd_file.
put_line (fnd_file.output, i || ') ' || v_msg_data);
END LOOP;
END IF;
END;
/* Cancel Po line*/
ELSIF ( p_po_num IS NOT NULL
AND p_line_num IS NOT NULL
AND p_ship_num IS NULL)
THEN
BEGIN
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
MO_GLOBAL.INIT ('PO');
mo_global.set_policy_context ('S', v_org_id);
DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
fnd_file.
put_line (fnd_file.output,
'Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.
CONTROL_DOCUMENT (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
p_doc_type => v_doc_type,
p_doc_subtype => v_doc_subtype,
p_doc_id => v_po_header_id,
p_doc_num => NULL,
p_release_id => NULL,
p_release_num => NULL,
p_doc_line_id => NULL,
p_doc_line_num => p_line_num,
p_doc_line_loc_id => NULL,
p_doc_shipment_num => NULL,
p_action => v_action,
p_action_date => v_action_date,
p_cancel_reason => 'OLD PURCHASE ORDER',
p_cancel_reqs_flag => 'N',
p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL,
p_org_id => v_org_id);
COMMIT;
DBMS_OUTPUT.
PUT_LINE ('The Return Status of the API : ' || v_return_status);
fnd_file.
put_line (fnd_file.output,
'The Return Status of the API : ' || v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.
put_line (fnd_file.output, 'PO line '||p_line_num||'Cancelled for po : ' || p_po_num);
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
ROLLBACK;
FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG
LOOP
v_msg_data :=
FND_MSG_PUB.GET (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.PUT_LINE (i || ') ' || v_msg_data);
fnd_file.
put_line (fnd_file.output, i || ') ' || v_msg_data);
END LOOP;
END IF;
END;
/* Cancel Po shipment*/
ELSIF ( p_po_num IS NOT NULL
AND p_line_num IS NOT NULL
AND p_ship_num IS NOT NULL)
THEN
BEGIN
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
MO_GLOBAL.INIT ('PO');
mo_global.set_policy_context ('S', v_org_id);
DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
fnd_file.
put_line (fnd_file.output,
'Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.
CONTROL_DOCUMENT (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
p_doc_type => v_doc_type,
p_doc_subtype => v_doc_subtype,
p_doc_id => v_po_header_id,
p_doc_num => NULL,
p_release_id => NULL,
p_release_num => NULL,
p_doc_line_id => NULL,
p_doc_line_num => p_line_num,
p_doc_line_loc_id => v_line_loc_id,
p_doc_shipment_num => p_ship_num,
p_action => v_action,
p_action_date => v_action_date,
p_cancel_reason => 'OLD PURCHASE ORDER',
p_cancel_reqs_flag => 'N',
p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL,
p_org_id => v_org_id);
COMMIT;
DBMS_OUTPUT.
PUT_LINE ('The Return Status of the API : ' || v_return_status);
fnd_file.
put_line (fnd_file.output,
'The Return Status of the API : ' || v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.
put_line (fnd_file.output, 'PO Shipment: '||p_ship_num||'Cancelled for po line : ' ||p_line_num||'and for po : ' || p_po_num);
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
ROLLBACK;
FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG
LOOP
v_msg_data :=
FND_MSG_PUB.GET (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.PUT_LINE (i || ') ' || v_msg_data);
fnd_file.
put_line (fnd_file.output, i || ') ' || v_msg_data);
END LOOP;
END IF;
END;
END IF;
fnd_file.
put_line (fnd_file.output,'------------------------------Completed-------------------------------');
EXCEPTION
when v_user_excp then
fnd_file.
put_line (fnd_file.output,
'-----Please Pass correct value to cancel the PO---------' || p_po_num);
retcode:=1;
When v_po_cc then
fnd_file.
put_line (fnd_file.output,
'-----Po Already Cancelled---------' || p_po_num);
retcode:=1;
When v_po_line_cc then
fnd_file.
put_line (fnd_file.output,
'-----Po Line Already Cancelled---------' ||'PO:'|| p_po_num||' Line '||p_line_num);
retcode:=1;
When v_po_ship_cc then
fnd_file.
put_line (fnd_file.output,
'-----Po Shipment Already Cancelled---------' ||'PO:'|| p_po_num||' Line '||p_line_num||' Ship num:'||p_ship_num);
retcode:=1;
WHEN OTHERS
THEN
fnd_file.
put_line (fnd_file.output,
'-----Could Not Cancel the po---------' || p_po_num||'Due to'||SQLERRM);
retcode:=1;
END;
END kfsh_CanCEL_PO_PKG;
/