IT Life 資訊人生
常常忘東忘西~~ 紀錄做過的事情，建立信心里程碑~~ 寫給小孩老爸的故事~~
2024-01-24
[AR]How to mass inactive customer
--失效客戶
DECLARE
P_CUST_ACCOUNT_REC HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
X_RETURN_STATUS VARCHAR2(2000);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
P_ORG_ID NUMBER := 103;
P_CUST_ACCOUNT_ID NUMBER := 1747;
P_OBJECT_VERSION_NUMBER NUMBER;
BEGIN
BEGIN
SELECT DISTINCT HCA.OBJECT_VERSION_NUMBER
INTO P_OBJECT_VERSION_NUMBER
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCSA,
HZ_CUST_SITE_USES_ALL HCSU
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCSU.CUST_ACCT_SITE_ID = HCSA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
AND HCSA.ORG_ID = P_ORG_ID
AND HCA.CUST_ACCOUNT_ID = P_CUST_ACCOUNT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
-- Setting the Context --
MO_GLOBAL.INIT('AR');
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 0, --SYSADMIN
RESP_ID => 50539, --ACEXX_AR_SuperUser--用這個權限也可失效其他據點
RESP_APPL_ID => 222); --應收帳款管理系統
MO_GLOBAL.SET_POLICY_CONTEXT('S', P_ORG_ID);
FND_GLOBAL.SET_NLS_CONTEXT('AMERICAN');
-- Initializing the Mandatory API parameters
P_CUST_ACCOUNT_REC.CUST_ACCOUNT_ID := P_CUST_ACCOUNT_ID;
P_CUST_ACCOUNT_REC.STATUS := 'I';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_cust_account_v2pub.update_cust_account');
HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT(P_INIT_MSG_LIST => FND_API.G_TRUE,
P_CUST_ACCOUNT_REC => P_CUST_ACCOUNT_REC,
P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updation of Customer Account is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Object Version Number = ' ||
P_OBJECT_VERSION_NUMBER);
ELSE
DBMS_OUTPUT.PUT_LINE('Updation of Customer Account got failed : ' ||
X_MSG_DATA);
ROLLBACK;
FOR I IN 1 .. X_MSG_COUNT LOOP
X_MSG_DATA := FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F');
DBMS_OUTPUT.PUT_LINE(I || ') ' || X_MSG_DATA);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
2024-01-15
[工作備忘]美國貿易制裁API
分享一下貿易制裁API
網站
https://developer.trade.gov/apis
網站教學
https://developer.trade.gov/
流程
- 註冊
- 建立API Key
- 官網可以測試或者用Postman測試
- 接下來就能開始開發
2023-12-18
[PowerShell]複製檔案功能
說明
- 原始檔案格式為YYYYMMDD.txt
- 複製後檔案格式為YYYYMMDD_003.txt
- 並可以透過參數$targetDate 來決定要抓取天數
# 指定源目錄的路徑
$sourcePath = "D:\card\"
# 指定目標目錄的路徑
$destinationPath = "\\192.168.0.216\card\"
# 指定後輟
$suffix = "_003"
# 設定目標日期（例如：10天後）
$targetDate = (Get-Date).AddDays(-1).ToString("yyyyMMdd")
# 取得符合條件的檔案列表
$filesToCopy = Get-ChildItem -Path $sourcePath -Recurse | Where-Object {
$_.Extension -eq '.txt' -and $_.BaseName -match '^\d{8}$' -and $_.BaseName -ge $targetDate
}
# 進行檔案複製
foreach ($file in $filesToCopy) {
# 獲取目標檔案的新名稱（加上後輟）
$newFileName = $file.BaseName + $suffix + $file.Extension
$destinationFile = Join-Path $destinationPath $newFileName
# 顯示複製檔案到
Write-Host "複製檔案到：$destinationFile"
# 複製檔案
Copy-Item -Path $file.FullName -Destination $destinationFile -Force
}
Write-Host "複製完成。"
2023-11-24
[PowerShell]查詢AD相關屬性
# AD模組
Import-Module ActiveDirectory
# 指定多個 OU
$ouPaths = @(
"OU=AEG,DC=test,DC=local",
"OU=TW,DC=test,DC=local",
"OU=TJ,DC=test,DC=local",
"OU=LY,DC=test,DC=local"
)
# 结果輸出 CSV 文件
$exportPath = "D:\ADAccountList.csv"
# 初始化
$allUsers = @()
# 迴圈處理每個 OU
foreach ($ouPath in $ouPaths) {
$users = Get-ADUser -Filter * -Properties "sn", "givenName", "displayName", "telephoneNumber", "sAMAccountName", "mail", "PasswordNeverExpires", "Enabled", "AccountExpirationDate" -SearchBase $ouPath
$allUsers += $users
}
# 结果輸出 CSV 文件
$allUsers | Select-Object sn, givenName, displayName, telephoneNumber, sAMAccountName, mail, PasswordNeverExpires,
@{
Name = "Status"
Expression = {
# 依照 Enabled 屬性顯示在職或離職
if ($_.Enabled) {
"在職"
} else {
"離職"
}
}
},
@{
Name = "AccountExpirationDate"
Expression = {
# 格式化 AccountExpirationDate 為 yyyy/mm/dd
if ($_.AccountExpirationDate) {
$_.AccountExpirationDate.ToString("yyyy/MM/dd")
} else {
"未設置"
}
}
} | Export-Csv -Path $exportPath -NoTypeInformation -Encoding UTF8
Write-Host "文件路径: $exportPath"
2023-11-23
[Oracle]GL Journal Drill Down SQL Query
/************************************************************************************************
HISTORY
Date Authur Version Description
---------- --------- ------- -------------------------------------------------------------
2023/11/22 Yulin.Chen v1.01 備份一下程式超難寫
************************************************************************************************/
SELECT '資產_折舊' TYPE, --完成驗證GL 391224
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
FAA.ATTRIBUTE1 AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
FA_ADDITIONS_V FAA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = FAA.ASSET_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 140
AND XTE.ENTITY_CODE = 'DEPRECIATION'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT '資產_調整' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
FA_TRANSACTION_HEADERS FTH,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = FTH.TRANSACTION_HEADER_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 140
AND XTE.ENTITY_CODE = 'TRANSACTIONS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT '應收帳款_銷售商業發票與銷退折讓單' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
(SELECT DISTINCT RCTL.INTERFACE_LINE_ATTRIBUTE5
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE RCTL.INTERFACE_LINE_CONTEXT = 'eDCN Import'
AND RCTL.INTERFACE_LINE_ATTRIBUTE5 IS NOT NULL
AND RCTL.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID) AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XDL.UNROUNDED_ENTERED_DR ENTERED_DR, --20220803
XDL.UNROUNDED_ENTERED_CR ENTERED_CR, --20220803
XDL.UNROUNDED_ACCOUNTED_DR ACCOUNTED_DR, --20220803
XDL.UNROUNDED_ACCOUNTED_CR ACCOUNTED_CR, --20220803
--XEL.ENTERED_DR,抓到總數，如果改成XDL還是不行要分開抓 TAX 與 LINE --20220803
--XEL.ENTERED_CR, --20220803
--XEL.ACCOUNTED_DR, --20220803
--XEL.ACCOUNTED_CR, --20220803
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
NULL CSTCATEGORY,
MIC.CATEGORY_CONCAT_SEGS INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
nvl(XEL.DESCRIPTION, rctla.description) SUB_DESCRIPTION --add by v1.02
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
XLA_DISTRIBUTION_LINKS XDL, --20220728
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG, --20220728
RA_CUSTOMER_TRX_LINES_ALL RCTLA, --20220728
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220728
MTL_SYSTEM_ITEMS_B MSI, --20220728
MTL_ITEM_CATEGORIES_V MIC, --20220728
HZ_CUST_ACCOUNTS_ALL HCAA, --20220810
HZ_PARTIES HP --20220810
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RCTA.CUSTOMER_TRX_ID
AND XTE.LEDGER_ID = RCTA.SET_OF_BOOKS_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'TRANSACTIONS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220728
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220728
AND UPPER(GLH.JE_SOURCE) = 'RECEIVABLES' --20220728
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XEL.APPLICATION_ID = XDL.APPLICATION_ID
AND RCTLG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1 --20220728
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL' --20220728
AND RCTLG.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+) --20220728
AND RCTLG.CUSTOMER_TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID(+) --20220728
--AND RCTA.TRX_NUMBER = '577229'
AND RCTLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+) --20220728
AND RCTA.INTERFACE_HEADER_ATTRIBUTE10 = MSI.ORGANIZATION_ID(+) --20220728
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID(+) --20220728
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID(+) --20220728
AND (MIC.CATEGORY_SET_ID = 1100000021 OR MIC.CATEGORY_SET_ID IS NULL) --20220728
AND RCTA.BILL_TO_CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID --20220810
AND HCAA.PARTY_ID = HP.PARTY_ID --20220810
UNION ALL
SELECT '應收帳款_收款' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME, ----Add v1.01
NULL VENDOR_NAME,
NULL GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AR_CASH_RECEIPTS_ALL RCTA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
--GL_JE_LINES GLL --20220627
HZ_PARTIES HP, --Add v1.01
HZ_CUST_ACCOUNTS_ALL HCA --Add v1.01
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RCTA.CASH_RECEIPT_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'RECEIPTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
--AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
--AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND NVL(XEL.ENTERED_DR, -1) <> 0
AND NVL(XEL.ENTERED_CR, -1) <> 0
AND NVL(XEL.ACCOUNTED_DR, -1) <> 0
AND NVL(XEL.ACCOUNTED_CR, -1) <> 0
AND RCTA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+) --Add v1.01
AND HCA.PARTY_ID = HP.PARTY_ID(+) --Add v1.01
UNION ALL
SELECT '應收帳款_調整' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME, ----Add v1.01
NULL VENDOR_NAME,
NULL GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RA_CUSTOMER_TRX_ALL RCTA,
AR_ADJUSTMENTS_ALL ADJ,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
--GL_JE_LINES GLL --20220627
HZ_PARTIES HP, --Add v1.01
HZ_CUST_ACCOUNTS_ALL HCA --Add v1.01
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = ADJ.ADJUSTMENT_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'ADJUSTMENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
--AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
--AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND NVL(XEL.ENTERED_DR, -1) <> 0
AND NVL(XEL.ENTERED_CR, -1) <> 0
AND NVL(XEL.ACCOUNTED_DR, -1) <> 0
AND NVL(XEL.ACCOUNTED_CR, -1) <> 0
AND ADJ.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+) --Add v1.01
AND HCA.PARTY_ID = HP.PARTY_ID(+) --Add v1.01
UNION ALL
SELECT 'AP_付款' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
ACA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
ACA.VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AP_CHECKS_ALL ACA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = ACA.CHECK_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_PAYMENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT 'AP_採購商業發票' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
AIA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
(SELECT AILA.ATTRIBUTE15
FROM AP_INVOICE_LINES_ALL AILA
WHERE AILA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AILA.ATTRIBUTE15 IS NOT NULL
AND AILA.INVOICE_ID = AIA.INVOICE_ID
AND AILA.LINE_NUMBER = XEL.AE_LINE_NUM) AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
NULL ACCOUNTING_CLASS_CODE,
GCC.CODE_COMBINATION_ID,
NULL ORDER_NUMBER,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
PV.VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AP_INVOICES_ALL AIA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220627
PO_VENDORS PV --20220810
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = AIA.INVOICE_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND AIA.VENDOR_ID = PV.VENDOR_ID --20220810
UNION ALL
SELECT '手動_ACETW_GL/FA 新增' TYPE,
GLH.LEDGER_ID,
GLH.DEFAULT_EFFECTIVE_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--NULL ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
NULL CURRENCY_CODE,
GLL.ENTERED_DR,
GLL.ENTERED_CR,
GLL.ACCOUNTED_DR,
GLL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
NULL ENTITY_CODE,
NULL GL_TRANSFER_DATE,
NULL JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
NULL ACCOUNTING_CLASS_CODE,
GCC.CODE_COMBINATION_ID,
NULL ORDER_NUMBER,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
NULL SUB_DESCRIPTION
FROM GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_CODE_COMBINATIONS GCC
WHERE GLB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLH.LEDGER_ID = $param_SOB_ID$
AND UPPER(GLH.JE_SOURCE) IN ('MANUAL','AUTOCOPY')
AND TRUNC(GLH.DEFAULT_EFFECTIVE_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
UNION ALL
SELECT '成本管理系統_收貨' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RCV_TRANSACTIONS RT,
PO_LINES_ALL PLA, --20220531
PO_HEADERS_ALL PHA,
MTL_SYSTEM_ITEMS_B MSI, --20220531
MTL_ITEM_CATEGORIES_V MIC, --20220728
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'RCV_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND PLA.PO_LINE_ID = RT.PO_LINE_ID --20220531
AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID --20220531
AND MSI.ORGANIZATION_ID = 105 --20220531
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID --20220628
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID --20220628
AND MIC.CATEGORY_SET_ID = 1 --20220628
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'RECEIVING'
UNION ALL
SELECT '成本管理系統_存貨' TYPE, --訂單
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
SUBSTR(OTTT.DESCRIPTION, 0, INSTR(OTTT.DESCRIPTION, '-') - 1) DEPT,
FLV.ATTRIBUTE14 ITEM_CATEGORY_ATT14,
JRS.NAME SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
REPLACE(TO_CHAR(OOHA.ORDER_NUMBER, 99999999999), ' ', NULL) ORDER_NUMBER,
OOHA.TRANSACTIONAL_CURR_CODE TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
MIC2.CATEGORY_CONCAT_SEGS CSTCATEGORY,
MIC.CATEGORY_CONCAT_SEGS INVCATEGORY,
OOLA.UNIT_SELLING_PRICE,
OOLA.UNIT_COST,
NULL RECEIPT_NUMBER,
MMT.TRANSACTION_DATE,
MTT.TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT, --20220725
OE_ORDER_HEADERS_ALL OOHA,
OE_TRANSACTION_TYPES_TL OTTT,
OE_ORDER_LINES_ALL OOLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_ITEM_CATEGORIES_V MIC,
MTL_ITEM_CATEGORIES_V MIC2,
FND_LOOKUP_VALUES FLV,
JTF_RS_SALESREPS JRS,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = MMT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND MMT.TRX_SOURCE_LINE_ID = OOLA.LINE_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND OOLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_CONCAT_SEGS = FLV.LOOKUP_CODE(+)
AND FLV.LANGUAGE = 'ZHT'
AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND OTTT.LANGUAGE = 'US'
AND OOLA.SALESREP_ID = JRS.SALESREP_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC2.INVENTORY_ITEM_ID --20220711
AND MSI.ORGANIZATION_ID = MIC2.ORGANIZATION_ID --20220711
AND MIC2.CATEGORY_SET_ID = 1100000021 --20220711
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID --20220725
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'INVENTORY'
UNION ALL
SELECT '成本管理系統_存貨' TYPE, --非訂單
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
MMT.TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
MIC.CATEGORY_CONCAT_SEGS CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
MMT.TRANSACTION_DATE,
MTT.TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT, --20220725
MTL_SYSTEM_ITEMS_B MSI,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220627
MTL_ITEM_CATEGORIES_V MIC
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = MMT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND MMT.TRX_SOURCE_LINE_ID IS NULL
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID --20220711
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID --20220711
AND MIC.CATEGORY_SET_ID = 1100000021 --20220711
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID --20220725
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'INVENTORY'
UNION ALL
SELECT '成本管理系統_在製品' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME, --日记帐批
GLH.NAME, --日记帐名
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
WE.WIP_ENTITY_NAME TRANSACTION_SOURCE_NAME,
WT.CURRENCY_CODE TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
WT.TRANSACTION_DATE TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
XLA_DISTRIBUTION_LINKS XDL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
WIP_TRANSACTIONS WT,
WIP_ENTITIES WE,
WIP_DISCRETE_JOBS WDJ,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL
WHERE 1 = 1
AND XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID
AND XTE.SOURCE_ID_INT_1 = WT.TRANSACTION_ID
AND WT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'WIP_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLL.JE_LINE_NUM = IR.JE_LINE_NUM
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND GLH.JE_CATEGORY = 'WIP'
--AND XEH.APPLICATION_ID = XEL.APPLICATION_ID --20220725v1
--AND GLB.NAME = '成本管理系統 A 1470958 65619248'
--AND IR.JE_LINE_NUM = 7
--AND XEL.APPLICATION_ID = XDL.APPLICATION_ID --20220725v1
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
--AND WE.WIP_ENTITY_ID = 13957590
AND (NVL(XEL.ENTERED_DR, 0) <> 0 OR NVL(XEL.ENTERED_CR, 0) <> 0)
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
訂閱：
文章 (Atom)