--AP INVOICE API 開發
DECLARE
P_REQUEST_ID NUMBER;
P_BOOLEAN BOOLEAN;
P_PHASE VARCHAR2(200);
P_STATUS VARCHAR2(200);
P_DEV_PHASE VARCHAR2(200);
P_DEV_STATUS VARCHAR2(200);
P_MESSAGE VARCHAR2(200);
P_SUCCESS BOOLEAN := TRUE;
P_ERROR_CODE VARCHAR2(4000);
P_ORG_ID NUMBER := 136;
P_INVOICE_ID NUMBER;
P_INVOICE_ID2 NUMBER;
P_TAX BOOLEAN := TRUE;
P_VENDOR_ID NUMBER := 2468608; --(零用金)
P_VENDOR_SITE_ID NUMBER := 144524; --新北新莊
P_INVOICE_NUM VARCHAR2(20) := 'EX_' ||
TO_CHAR(SYSDATE, 'YYYYMMDD') ||
'_018';
P_INVOICE_AMOUNT NUMBER := 1200;
P_INVOICE_CURRENCY_CODE VARCHAR2(5) := 'TWD';
P_INVOICE_DATE DATE := TRUNC(SYSDATE);
P_DESCRIPTION_H VARCHAR2(200) := '2023.02 零用金支出';
P_DESCRIPTION_L VARCHAR2(200) := '2023.01 業務帳單郵務費';
P_TERMS_ID NUMBER := 10000; --付款條件
P_CREATED_BY NUMBER := 0;
P_ATTRIBUTE4 VARCHAR2(5) := '否';
P_ATTRIBUTE6 VARCHAR2(5) := 'NO';
P_DIST_CODE_COMBINATION_ID NUMBER := 79040;
P_TAX_CLASSIFICATION_CODE VARCHAR2(10) := 'VAT-5%';
--P_TAX_CLASSIFICATION_CODE VARCHAR2(10) := NULL;
P_BPM_APPLY_NUM VARCHAR2(10) := 'BPM_1234';
BEGIN
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO P_INVOICE_ID FROM DUAL;
INSERT INTO AP_INVOICES_INTERFACE
(INVOICE_ID,
INVOICE_TYPE_LOOKUP_CODE,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_NUM,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
INVOICE_DATE,
DESCRIPTION,
PAY_GROUP_LOOKUP_CODE,
SOURCE,
TERMS_ID,
ORG_ID,
ATTRIBUTE4,
ATTRIBUTE6,
CREATION_DATE,
CREATED_BY,
GROUP_ID)
VALUES
(P_INVOICE_ID,
'STANDARD',
P_VENDOR_ID,
P_VENDOR_SITE_ID,
P_INVOICE_NUM,
P_INVOICE_AMOUNT,
P_INVOICE_CURRENCY_CODE,
P_INVOICE_DATE,
P_DESCRIPTION_H,
NULL,
'MANUAL INVOICE ENTRY', --SELECT * FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE'
P_TERMS_ID,
P_ORG_ID,
P_ATTRIBUTE4,
P_ATTRIBUTE6,
SYSDATE,
P_CREATED_BY,
P_BPM_APPLY_NUM);
INSERT INTO AP_INVOICE_LINES_INTERFACE
(INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
DESCRIPTION,
ACCOUNTING_DATE,
DIST_CODE_COMBINATION_ID,
CREATION_DATE,
CREATED_BY,
TAX_CLASSIFICATION_CODE)
VALUES
(P_INVOICE_ID,
AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
1,
'ITEM',
P_INVOICE_AMOUNT,
P_DESCRIPTION_L,
SYSDATE,
P_DIST_CODE_COMBINATION_ID,
SYSDATE,
P_CREATED_BY,
P_TAX_CLASSIFICATION_CODE);
BEGIN
MO_GLOBAL.INIT('SQLAP');
MO_GLOBAL.SET_POLICY_CONTEXT('S', 136); --OU_ID
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 9123,
RESP_ID => 50540, --ACETW_AP_SuperUser
RESP_APPL_ID => 200); --AP
FND_REQUEST.SET_ORG_ID(136);
--Call request Payables Open Interface Import
P_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'SQLAP',
PROGRAM => 'APXIIMPT',
DESCRIPTION => NULL,
START_TIME => NULL,
SUB_REQUEST => NULL,
ARGUMENT1 => NULL, --Operating Unit
ARGUMENT2 => 'MANUAL INVOICE ENTRY', --Source
ARGUMENT3 => P_BPM_APPLY_NUM, --Group
ARGUMENT4 => '2023 (零用金)', --Batch Name
ARGUMENT5 => NULL, --Hold Name
ARGUMENT6 => NULL, --Hold Reason
ARGUMENT7 => NULL, --GL Date
ARGUMENT8 => 'Y', --If Success Purge interface data
ARGUMENT9 => 'N', --Trace Switch
ARGUMENT10 => 'N', --Debug Switch
ARGUMENT11 => 'Y', --Summarize Report for Audit report
ARGUMENT12 => 1000, --Commit Batch Size
ARGUMENT13 => NULL, --User ID
ARGUMENT14 => NULL, --Login ID
ARGUMENT15 => 'N' --Skip Validation
);
COMMIT;
IF P_REQUEST_ID > 0 THEN
P_BOOLEAN := FND_CONCURRENT.WAIT_FOR_REQUEST(P_REQUEST_ID, --request_id IN number default NULL,
5, --Interval IN number default 60, SECONDS
0, --max_wait IN number default 0,
P_PHASE, --phase OUT varchar2,
P_STATUS, --status OUT varchar2,
P_DEV_PHASE, --dev_phase OUT varchar2,
P_DEV_STATUS, --dev_status OUT varchar2,,
P_MESSAGE --message OUT varchar2) return boolean
);
END IF;
SELECT AIA.INVOICE_ID
INTO P_INVOICE_ID2
FROM AP_INVOICES_ALL AIA
WHERE INVOICE_NUM = P_INVOICE_NUM;
--DBMS_OUTPUT.PUT_LINE(P_STATUS);
DBMS_OUTPUT.PUT_LINE('REQUEST:' || P_REQUEST_ID);
DBMS_OUTPUT.PUT_LINE('INVOICE_ID:' || P_INVOICE_ID2);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE || '-' || SQLERRM);
END;
END;
/* BPM 使用
查詢 BATCH_NAME 選擇
SELECT DISTINCT BATCH_NAME
FROM AP_BATCHES_ALL ABA, AP_INVOICES_ALL AIA
WHERE 1 = 1
AND ABA.BATCH_ID = AIA.BATCH_ID
AND AIA.ORG_ID = 136
AND SUBSTR(BATCH_NAME, 0, 4) = TO_CHAR(SYSDATE, 'YYYY')
ORDER BY BATCH_NAME
查詢 供應商、付款條件
SELECT VD.VENDOR_ID,
VD.VENDOR_NAME,
VS.VENDOR_SITE_ID,
VS.VENDOR_SITE_CODE,
VS.TERMS_ID,
PT.NAME
FROM AP_SUPPLIERS VD, AP_SUPPLIER_SITES_ALL VS, AP_TERMS PT
WHERE 1 = 1
AND VD.VENDOR_ID = VS.VENDOR_ID(+)
AND VS.ORG_ID = 136
AND VD.ENABLED_FLAG <> 'N'
AND VS.TERMS_ID = PT.TERM_ID
AND PT.ENABLED_FLAG <> 'N'
AND TRUNC(NVL(PT.END_DATE_ACTIVE, SYSDATE + 1)) > TRUNC(SYSDATE)
查詢會計科目ID
SELECT GCC.CODE_COMBINATION_ID,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4
FROM GL_CODE_COMBINATIONS GCC
WHERE 1 = 1
AND GCC.CODE_COMBINATION_ID = 79040
AND GCC.ENABLED_FLAG <> 'N'
AND GCC.SEGMENT1 = '90'
AND GCC.SEGMENT5 = '000000'
AND GCC.SEGMENT6 = '000'
AND TRUNC(NVL(GCC.END_DATE_ACTIVE, SYSDATE + 1)) > TRUNC(SYSDATE)
查詢AP建立人員
SELECT FU.USER_ID, FU.USER_NAME
FROM FND_USER FU
WHERE 1 = 1
AND FU.USER_NAME IN ('郭', '李', '陳')
*/
/*
SELECT ACE_UTL_TOOLS.GET_ERPUSER_PASSWORD('郭') FROM DUAL
SELECT * FROM AP_INVOICE_LINES_INTERFACE
SELECT * FROM AP_INVOICES_ALL A WHERE A.BATCH_ID IN (765006)
SELECT * FROM AP_INVOICES_INTERFACE
UPDATE AP_INVOICE_LINES_ALL A
SET A.TAX_CLASSIFICATION_CODE = 'VAT-5%'
WHERE A.INVOICE_ID = 1455593
SELECT AIH.STATUS,
AIH.INVOICE_ID,
INVOICE_NUM,
AIL.TAX_CODE,
AIL.TAX_RATE_CODE,
--AIR.*,
AIH.*,
AIL.*
FROM AP_INVOICES_INTERFACE AIH,
AP_INVOICE_LINES_INTERFACE AIL,
AP_INTERFACE_REJECTIONS AIR
WHERE 1 = 1
AND AIH.INVOICE_ID = AIL.INVOICE_ID(+)
AND AIL.INVOICE_LINE_ID = AIR.PARENT_ID(+)
AND TO_CHAR(AIH.CREATION_DATE, 'YYYYMM') = '202308'
AND INVOICE_NUM LIKE '%EX_%'
AND AIH.STATUS IS NULL
DELETE AP_INVOICES_INTERFACE WHERE INVOICE_ID = 188387;
DELETE AP_INVOICE_LINES_INTERFACE WHERE INVOICE_ID = 188387;
DELETE AP_INVOICES_INTERFACE
DELETE AP_INVOICE_LINES_INTERFACE
DELETE AP_INTERFACE_REJECTIONS
SELECT * FROM FND_USER WHERE USER_NAME = '陳'
*/
--計算稅捐
DECLARE
P_SUCCESS BOOLEAN := TRUE;
P_INVOICE_ID NUMBER := 1459599;
P_ERROR_CODE VARCHAR2(4000);
BEGIN
P_SUCCESS := AP_ETAX_PKG.CALLING_ETAX(P_INVOICE_ID => P_INVOICE_ID,
P_CALLING_MODE => 'CALCULATE',
P_ALL_ERROR_MESSAGES => 'N',
P_ERROR_CODE => P_ERROR_CODE,
P_CALLING_SEQUENCE => 'APXINWKB -> Inv_Sum_Control.Calculate_Tax');
COMMIT;
IF NOT (P_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Reason:' || P_ERROR_CODE);
DBMS_OUTPUT.PUT_LINE('Error:' || SQLCODE || '-' || SQLERRM);
END IF;
END;
SELECT * FROM AP_INVOICES_ALL A WHERE A.ATTRIBUTE1 IS NOT NULL
沒有留言:
張貼留言