/* Procedure to Cancel the PO LINES */
CREATE OR REPLACE PROCEDURE APPS.XX_PO_CANCEL_PRG (
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_ORG IN NUMBER,
P_P0_HEADER_ID IN NUMBER,
P_VENDER_ID IN NUMBER,
P_RELEASE_NUMBER IN NUMBER,
P_ITEM_ID IN NUMBER,
P_SHIPMENT_NUMBER IN NUMBER
)
IS
CURSOR C_PO_CANCEL
IS
SELECT PHA.TYPE_LOOKUP_CODE,
PHA.PO_HEADER_ID,
PLA.PO_LINE_ID,
PLA.LINE_NUM,
PRA.RELEASE_NUM,
PRA.PO_RELEASE_ID,
PLLA.LINE_LOCATION_ID,
PLLA.SHIPMENT_NUM,
PLA.ITEM_ID,
PLA.ITEM_DESCRIPTION,
PHA.SEGMENT1 PO_NUMBER,
PHA.ORG_ID,
PRA.AUTHORIZATION_STATUS,
NVL (PLLA.QUANTITY, 0) ORDER_QTY,
NVL (PLLA.QUANTITY_RECEIVED, 0) QTY_REC,
PHA.VENDOR_ID,
VENDOR_TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_RELEASES_ALL PRA,
AP_SUPPLIERS AP
WHERE 1 = 1
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.ORG_ID = PRA.ORG_ID
AND PHA.PO_HEADER_ID = NVL (P_P0_HEADER_ID, PHA.PO_HEADER_ID)
AND PHA.VENDOR_ID = NVL (P_VENDER_ID, PHA.VENDOR_ID)
AND PHA.VENDOR_ID = AP.VENDOR_ID
AND PRA.RELEASE_NUM = NVL (P_RELEASE_NUMBER, PRA.RELEASE_NUM)
AND PHA.TYPE_LOOKUP_CODE = 'BLANKET'
AND PRA.PO_RELEASE_ID = PLLA.PO_RELEASE_ID
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.ITEM_ID = NVL (P_ITEM_ID, PLA.ITEM_ID)
AND PLLA.SHIPMENT_NUM =
NVL (P_SHIPMENT_NUMBER, PLLA.SHIPMENT_NUM)
AND PHA.ORG_ID = NVL (P_ORG, PHA.ORG_ID)
AND PLLA.CANCEL_FLAG = 'N'
AND PRA.AUTHORIZATION_STATUS IN
('INCOMPLETE', 'APPROVED', 'IN PROCESS')
AND NVL (PLLA.QUANTITY, 0) - NVL (PLLA.QUANTITY_RECEIVED, 0) >
0; --AND ROWNUM > 500;
P_API_VERSION NUMBER;
P_INIT_MSG_LIST VARCHAR2 (32767);
P_COMMIT VARCHAR2 (32767);
X_RETURN_STATUS VARCHAR2 (32767);
P_DOC_TYPE VARCHAR2 (25);
P_DOC_SUBTYPE VARCHAR2 (25);
P_DOC_ID NUMBER;
P_DOC_NUM VARCHAR2 (20);
P_RELEASE_ID NUMBER;
P_RELEASE_NUM NUMBER;
P_DOC_LINE_ID NUMBER;
P_DOC_LINE_NUM NUMBER;
P_DOC_LINE_LOC_ID NUMBER;
P_DOC_SHIPMENT_NUM NUMBER;
P_ACTION VARCHAR2 (32767);
P_ACTION_DATE DATE;
P_CANCEL_REASON VARCHAR2 (240);
P_CANCEL_REQS_FLAG VARCHAR2 (32767);
P_PRINT_FLAG VARCHAR2 (32767);
P_NOTE_TO_VENDOR VARCHAR2 (480);
P_USE_GLDATE VARCHAR2 (32767);
P_ORG_ID NUMBER;
BEGIN
INSERT INTO po_rel_performance_chk (start_date)
VALUES (SYSDATE);
COMMIT;
MO_GLOBAL.INIT ('PO');
FND_GLOBAL.APPS_INITIALIZE (FND_PROFILE.VALUE ('USER_ID'), 50372, 7000);
FOR I IN C_PO_CANCEL
LOOP
FND_MSG_PUB.INITIALIZE;
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'PO NUMBER TO CANCEL IS :'
|| '-'
|| I.PO_NUMBER
|| '-'
|| I.RELEASE_NUM
|| '-'
|| I.ITEM_DESCRIPTION
|| I.QTY_REC
);
IF I.QTY_REC = 0 AND I.AUTHORIZATION_STATUS = 'APPROVED'
THEN
P_API_VERSION := 1.0;
P_INIT_MSG_LIST := FND_API.G_TRUE;
P_COMMIT := FND_API.G_TRUE;
X_RETURN_STATUS := X_RETURN_STATUS;
P_DOC_TYPE := 'RELEASE';
P_DOC_SUBTYPE := 'BLANKET';
P_DOC_ID := I.PO_HEADER_ID;
P_DOC_NUM := NULL;
P_RELEASE_ID := NULL;
P_RELEASE_NUM := I.RELEASE_NUM;
P_DOC_LINE_ID := NULL;
P_DOC_LINE_NUM := NULL;
P_DOC_LINE_LOC_ID := I.LINE_LOCATION_ID;
P_DOC_SHIPMENT_NUM := I.SHIPMENT_NUM;
P_ACTION := 'CANCEL';
P_ACTION_DATE := NULL;
P_CANCEL_REASON := 'API';
P_CANCEL_REQS_FLAG := 'N';
P_PRINT_FLAG := NULL;
P_NOTE_TO_VENDOR := NULL;
P_USE_GLDATE := NULL;
P_ORG_ID := I.ORG_ID;
APPS.PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT (P_API_VERSION,
P_INIT_MSG_LIST,
P_COMMIT,
X_RETURN_STATUS,
P_DOC_TYPE,
P_DOC_SUBTYPE,
P_DOC_ID,
P_DOC_NUM,
P_RELEASE_ID,
P_RELEASE_NUM,
P_DOC_LINE_ID,
P_DOC_LINE_NUM,
P_DOC_LINE_LOC_ID,
P_DOC_SHIPMENT_NUM,
P_ACTION,
P_ACTION_DATE,
P_CANCEL_REASON,
P_CANCEL_REQS_FLAG,
P_PRINT_FLAG,
P_NOTE_TO_VENDOR,
P_USE_GLDATE,
P_ORG_ID);
DBMS_OUTPUT.PUT_LINE ('X_RETURN_STATUS = ' || X_RETURN_STATUS);
IF X_RETURN_STATUS = 'S'
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'The Purchase Order Which is Cancelled Now => ' || I.PO_NUMBER
);
BEGIN
COMMIT;
END;
ELSE
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'The Purchase Order'
|| I.PO_NUMBER
|| 'Failed to cancel Due To Following Reason'
);
FOR J IN 1 .. FND_MSG_PUB.COUNT_MSG
LOOP
DBMS_OUTPUT.PUT_LINE (
FND_MSG_PUB.GET (P_MSG_INDEX => J, P_ENCODED => 'F')
);
END LOOP;
END IF;
ELSIF I.QTY_REC = 0 AND I.AUTHORIZATION_STATUS = 'INCOMPLETE'
THEN
DELETE FROM PO_RELEASES_ALL PRA
WHERE PRA.PO_RELEASE_ID = I.PO_RELEASE_ID
AND PRA.ORG_ID = I.ORG_ID;
DELETE FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.PO_RELEASE_ID = I.PO_RELEASE_ID
AND POD.ORG_ID = I.ORG_ID;
ELSE
UPDATE PO_LINE_LOCATIONS_ALL A
SET A.QUANTITY = A.QUANTITY_RECEIVED
WHERE A.LINE_LOCATION_ID = I.LINE_LOCATION_ID
AND A.ORG_ID = I.ORG_ID;
UPDATE PO_DISTRIBUTIONS_ALL B
SET QUANTITY_ORDERED = I.ORDER_QTY
WHERE B.LINE_LOCATION_ID = I.LINE_LOCATION_ID
AND B.ORG_ID = I.ORG_ID;
COMMIT;
END IF;
END LOOP;
UPDATE po_rel_performance_chk
SET end_date = SYSDATE
WHERE start_date IS NOT NULL;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'no-data-found');
END;
/
No comments:
Post a Comment