DECLARE
P_TYPE_NUMBER NUMBER := 0; --IN --10 取消訂單 、20 變更交期 、30 變更付款條件
P_BPM_APPLY_NAME VARCHAR2(50) :='XXX'; --IN Oracle Account Name
P_OU_ID NUMBER := 136; --IN Oracle OU ID
P_HEADER_ID NUMBER := 1234; --IN Oracle OOHA.HEADER_ID
P_LINE_ID NUMBER := 5678; --IN Oracle OOLA.HEADER_ID
P_STATUS_CODE VARCHAR2(500); --OUT Status
P_DATA VARCHAR2(10) := NULL;
--P_TYPE_NUMBER NUMBER := 0; --IN --10 取消訂單 、20 變更交期 、30 變更付款條件
--P_BPM_APPLY_NAME VARCHAR2(50); --IN
V_BPM_APPLY_ID NUMBER;
V_SCHEDULE_SHIP_DATE DATE;
V_NEW_SCHEDULE_SHIP_DATE DATE;
V_API_VERSION_NUMBER NUMBER := 1;
V_RETURN_STATUS VARCHAR2(2000);
V_MSG_COUNT NUMBER;
V_MSG_DATA VARCHAR2(2000);
L_DEBUG_LEVEL NUMBER := 1;
L_DEBUG_FILE VARCHAR2(100);
-- IN Variables
V_HEADER_REC OE_ORDER_PUB.HEADER_REC_TYPE;
V_LINE_TBL OE_ORDER_PUB.LINE_TBL_TYPE;
V_ACTION_REQUEST_TBL OE_ORDER_PUB.REQUEST_TBL_TYPE;
V_LINE_ADJ_TBL OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
--OUT Variables
V_HEADER_REC_OUT OE_ORDER_PUB.HEADER_REC_TYPE;
V_HEADER_VAL_REC_OUT OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
V_HEADER_ADJ_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
V_HEADER_ADJ_VAL_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
V_HEADER_PRICE_ATT_TBL_OUT OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
V_HEADER_ADJ_ATT_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
V_HEADER_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
V_HEADER_SCREDIT_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
V_HEADER_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
V_LINE_TBL_OUT OE_ORDER_PUB.LINE_TBL_TYPE;
V_LINE_VAL_TBL_OUT OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
V_LINE_ADJ_TBL_OUT OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
V_LINE_ADJ_VAL_TBL_OUT OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
V_LINE_PRICE_ATT_TBL_OUT OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
V_LINE_ADJ_ATT_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
V_LINE_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
V_LINE_SCREDIT_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
V_LINE_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
V_LOT_SERIAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
V_LOT_SERIAL_VAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
V_ACTION_REQUEST_TBL_OUT OE_ORDER_PUB.REQUEST_TBL_TYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF (L_DEBUG_LEVEL > 0) THEN
L_DEBUG_FILE := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
OE_DEBUG_PUB.INITIALIZE;
OE_DEBUG_PUB.SETDEBUGLEVEL(L_DEBUG_LEVEL);
OE_MSG_PUB.INITIALIZE;
END IF;
MO_GLOBAL.INIT('ONT');
--MO_GLOBAL.SET_POLICY_CONTEXT('S', 136); --136 OU_ID
MO_GLOBAL.SET_POLICY_CONTEXT('S', P_OU_ID); --136 OU_ID
IF P_TYPE_NUMBER = 10 THEN
FND_GLOBAL.APPS_INITIALIZE(0, 50530, 660);
ELSIF P_TYPE_NUMBER = 20 THEN
BEGIN
SELECT USER_ID
INTO V_BPM_APPLY_ID
FROM FND_USER
WHERE USER_NAME = P_BPM_APPLY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_BPM_APPLY_ID := 0; --因無介接關係，如找不到先壓0
END;
FND_GLOBAL.APPS_INITIALIZE(V_BPM_APPLY_ID, 50530, 660);
END IF;
V_ACTION_REQUEST_TBL(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
IF P_TYPE_NUMBER = 10 THEN
--Cancel a Line Record
V_LINE_TBL(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
V_LINE_TBL(1).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
V_LINE_TBL(1).HEADER_ID := P_HEADER_ID; --2200108; --91122050119
V_LINE_TBL(1).LINE_ID := P_LINE_ID; --3589805;
V_LINE_TBL(1).ORDERED_QUANTITY := 0;
V_LINE_TBL(1).CANCELLED_FLAG := 'Y';
V_LINE_TBL(1).CANCELLED_QUANTITY := 0;
V_LINE_TBL(1).CHANGE_REASON := 'B14交期超過30天'; --依照規格書定義取消原因
ELSIF P_TYPE_NUMBER = 20 THEN
--因工作曆設定工作日為星期一到星期五，當作後一天遇到六日時會回推到星期五
SELECT NVL(OOLA.SCHEDULE_SHIP_DATE, SYSDATE)
INTO V_SCHEDULE_SHIP_DATE
FROM OE_ORDER_LINES_ALL OOLA
WHERE OOLA.HEADER_ID = P_HEADER_ID
AND OOLA.LINE_ID = P_LINE_ID;
--SELECT ADD_MONTHS(NVL(OOLA.SCHEDULE_SHIP_DATE, SYSDATE), 1) INTO V_NEW_SCHEDULE_SHIP_DATE FROM OE_ORDER_LINES_ALL OOLA WHERE OOLA.HEADER_ID = P_HEADER_ID AND OOLA.LINE_ID = P_LINE_ID;
IF V_SCHEDULE_SHIP_DATE > SYSDATE THEN
V_NEW_SCHEDULE_SHIP_DATE := V_SCHEDULE_SHIP_DATE + 30;
ELSE
V_NEW_SCHEDULE_SHIP_DATE := SYSDATE + 30;
END IF;
--Change Schedule_Ship_Date
V_LINE_TBL(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
V_LINE_TBL(1).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
V_LINE_TBL(1).HEADER_ID := P_HEADER_ID; --2200108; --91122050119
V_LINE_TBL(1).LINE_ID := P_LINE_ID; --3589807;
--V_LINE_TBL(1).SCHEDULE_SHIP_DATE := TO_DATE('2022/11/11 23:59:59','YYYY/MM/DD HH24:MI:SS');
V_LINE_TBL(1).SCHEDULE_SHIP_DATE := V_NEW_SCHEDULE_SHIP_DATE;
--begin v1.02 add
ELSIF p_type_number = 30 THEN
fnd_global.apps_initialize(0, 50530, 660);
--Change Schedule_Ship_Date
v_line_tbl(1) := oe_order_pub.g_miss_line_rec;
v_line_tbl(1).operation := oe_globals.g_opr_update;
v_line_tbl(1).header_id := p_header_id;
v_line_tbl(1).line_id := p_line_id;
BEGIN
SELECT term_id
INTO v_line_tbl(1).payment_term_id
FROM ra_terms
WHERE name = p_data
AND (end_date_active IS NULL OR
end_date_active >= trunc(sysdate));
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
--end v1.02 add
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE('Starting of API');
--Calling the API to cancel a line from an Existing Order
OE_ORDER_PUB.PROCESS_ORDER(P_API_VERSION_NUMBER => V_API_VERSION_NUMBER,
P_HEADER_REC => V_HEADER_REC,
P_LINE_TBL => V_LINE_TBL,
P_ACTION_REQUEST_TBL => V_ACTION_REQUEST_TBL,
P_LINE_ADJ_TBL => V_LINE_ADJ_TBL, --OUT VARIABLES,
X_HEADER_REC => V_HEADER_REC_OUT,
X_HEADER_VAL_REC => V_HEADER_VAL_REC_OUT,
X_HEADER_ADJ_TBL => V_HEADER_ADJ_TBL_OUT,
X_HEADER_ADJ_VAL_TBL => V_HEADER_ADJ_VAL_TBL_OUT,
X_HEADER_PRICE_ATT_TBL => V_HEADER_PRICE_ATT_TBL_OUT,
X_HEADER_ADJ_ATT_TBL => V_HEADER_ADJ_ATT_TBL_OUT,
X_HEADER_ADJ_ASSOC_TBL => V_HEADER_ADJ_ASSOC_TBL_OUT,
X_HEADER_SCREDIT_TBL => V_HEADER_SCREDIT_TBL_OUT,
X_HEADER_SCREDIT_VAL_TBL => V_HEADER_SCREDIT_VAL_TBL_OUT,
X_LINE_TBL => V_LINE_TBL_OUT,
X_LINE_VAL_TBL => V_LINE_VAL_TBL_OUT,
X_LINE_ADJ_TBL => V_LINE_ADJ_TBL_OUT,
X_LINE_ADJ_VAL_TBL => V_LINE_ADJ_VAL_TBL_OUT,
X_LINE_PRICE_ATT_TBL => V_LINE_PRICE_ATT_TBL_OUT,
X_LINE_ADJ_ATT_TBL => V_LINE_ADJ_ATT_TBL_OUT,
X_LINE_ADJ_ASSOC_TBL => V_LINE_ADJ_ASSOC_TBL_OUT,
X_LINE_SCREDIT_TBL => V_LINE_SCREDIT_TBL_OUT,
X_LINE_SCREDIT_VAL_TBL => V_LINE_SCREDIT_VAL_TBL_OUT,
X_LOT_SERIAL_TBL => V_LOT_SERIAL_TBL_OUT,
X_LOT_SERIAL_VAL_TBL => V_LOT_SERIAL_VAL_TBL_OUT,
X_ACTION_REQUEST_TBL => V_ACTION_REQUEST_TBL_OUT,
X_RETURN_STATUS => V_RETURN_STATUS,
X_MSG_COUNT => V_MSG_COUNT,
X_MSG_DATA => V_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF V_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
--begin v1.02 add
IF p_type_number = 30 THEN
UPDATE oe_order_headers_all ooha
SET payment_term_id = v_line_tbl(1).payment_term_id
WHERE header_id = p_header_id;
END IF;
--end v1.02 add
COMMIT;
DBMS_OUTPUT.PUT_LINE('Order is Success');
P_STATUS_CODE := 'S';
ELSE
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Order failed:' || V_MSG_DATA);
P_STATUS_CODE := 'F';
FOR I IN 1 .. V_MSG_COUNT LOOP
V_MSG_DATA := OE_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F');
DBMS_OUTPUT.PUT_LINE(I || ') ' || V_MSG_DATA);
END LOOP;
END IF;
END;
2022-12-29
[Oracle]OM API
訂閱：
張貼留言 (Atom)
沒有留言:
張貼留言