Purchase Order Cancellation Interface

-- ---------------------------------------------------------------------------
-- Purpose : Purchase order Cancellation validations
-- Date      : 26.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


CREATE OR REPLACE PROCEDURE xx_po_can_validation(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2):=0;
l_header_id NUMBER:=0;
l_line_id NUMBER:=0;
l_flag VARCHAR2(1):=NULL;
CURSOR c_po_cancel_valid IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status IN ('E','T')
FOR UPDATE;
BEGIN
FOR c_po_cancel_valid_rec IN c_po_cancel_valid
LOOP
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_count=0 THEN
UPDATE xx_tab_po_can_stg
SET row_status='E'
,error_message='This Slip number,Po number is not existing'
WHERE CURRENT OF c_po_cancel_valid;
ELSIF l_count>0 THEN
SELECT pla.cancel_flag INTO l_flag
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_flag='Y' THEN
UPDATE xx_tab_po_can_stg
SET row_status='R'
,error_message='This slipno and pono combination is already closed'
WHERE CURRENT OF c_po_cancel_valid;
ELSE
SELECT pha.po_header_id
,pla.po_line_id
INTO l_header_id
,l_line_id
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
UPDATE xx_tab_po_can_stg
SET header_id=l_header_id
,line_id=l_line_id
,row_status='V'
WHERE CURRENT OF c_po_cancel_valid;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_validation;
/


--======================================================
--Cancelling the Valid Purchase order lines
--======================================================

CREATE OR REPLACE PROCEDURE xx_po_can_import(errbuf out varchar2, retcode out varchar2)
AS
i NUMBER:=0;
x NUMBER:=0;
x_return_status VARCHAR2(1);
p_look_up_code VARCHAR2(10);
p_user_id NUMBER:=0;
p_resp_id NUMBER:=0;
p_resp_appl_id NUMBER:=0;
CURSOR c_po_cancel_import IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status='V';
BEGIN
SELECT meai_po_cancel_seq.NEXTVAL INTO x
FROM DUAL;
SELECT fnd_profile.value('user_id') INTO p_user_id from dual;
SELECT fnd_profile.value('resp_id') INTO p_resp_id from dual;
SELECT fnd_profile.value('resp_appl_id') INTO p_resp_appl_id from dual;
FND_GLOBAL.apps_initialize ( user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_resp_appl_id);
BEGIN
FOR c_po_cancel_import_rec IN c_po_cancel_import
LOOP
SELECT
distinct(type_lookup_code)
into p_look_up_code
from po_headers_all
where po_header_id = c_po_cancel_import_rec.header_id;
PO_Document_Control_PUB.control_document(
1.0,
FND_API.G_TRUE,
FND_API.G_TRUE,
x_return_status,
'PO',
p_look_up_code,
c_po_cancel_import_rec.header_id,
NULL,
NULL,
NULL,
c_po_cancel_import_rec.line_id,
NULL,
NULL,
NULL,
'CANCEL',
c_po_cancel_import_rec.cancelled_date,
NULL,
'N',
NULL,
NULL,
NULL
);
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
FND_FILE.put_line(FND_FILE.LOG,FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F'));
END LOOP;
END LOOP;
DECLARE
l_cnt number:=0;
CURSOR c_po_valid IS
SELECT Distinct
RPAD(po_header_reference,16,' ') po_header_reference,
RPAD(ROW_STATUS,8,' ') ROW_STATUS,
RPAD(ERROR_MESSAGE,200,' ') ERROR_MESSAGE
FROM xx_tab_po_can_stg;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.output,'************************|Purchase Order Cancellation Interface Report|****************************');
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' po_header_reference '||' '||' ROW_STATUS '||' '||' ERROR_MESSAGE');
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
l_cnt:=0;
FOR l_index IN c_po_valid
LOOP
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_index.po_header_reference||' '||l_index.ROW_STATUS||' '||l_index.ERROR_MESSAGE);
l_cnt:=l_cnt+1;
END LOOP ; -- Cursor loop
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,l_cnt||' '||'Purchase Order(s) Processed..');
END;
INSERT INTO xx_tab_po_can_err(
po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,date_of_creation
,batch_id)
SELECT po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,SYSDATE
,x
FROM xx_tab_po_can_stg;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE xx_tab_po_can_stg;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_import;
/


Note: This program only will cancel the PO Lines, order has to be cancelled manually.

-- ---------------------------------------------------------------------------