--Mass Update BPA Price
--How to use the PO Cancel API - PO_Document_Control_PUB (Doc ID 833925.1)
--FAQ: Purchasing Documents Open Interface (Doc ID 224031.1)
DECLARE
X_RETURN_STATUS VARCHAR(240);
P_REQUEST_ID NUMBER;
P_MESSAGE VARCHAR(2000);
/* YOLIN_TEMP
IN RPT_ID BATCH_ID
IN RPT_ID 流水號
IN ATTR1 頁籤
IN ATTR2 供應商名稱
IN ATTR3 廠商料號
IN ATTR4 更新價格
OUT ATTR5 PO_HEADER_ID
OUT ATTR6 PO_LINE_ID
OUT ATTR7 INVENTORY_ITEM_ID
OUT ATTR8 原始價格
OUT ATTR9 回寫更新狀態 Y
*/
CURSOR IMPORT_PRICE IS
SELECT RPT_ID, LINE_NO, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, ATTR6, ATTR7, ATTR8, ATTR9
FROM YOLIN_TEMP
WHERE 1 = 1
--AND LINE_NO <= 10
AND RPT_ID = '20230329';
CURSOR BPA(P_VENDOR_NAME VARCHAR2, P_VENDOR_ITEM VARCHAR2) IS
SELECT PHA.PO_HEADER_ID,
PLA.PO_LINE_ID,
PHA.AGENT_ID,
PHA.SHIP_TO_LOCATION_ID,
PHA.BILL_TO_LOCATION_ID,
PHA.TYPE_LOOKUP_CODE,
MSI.ORGANIZATION_ID,
PHA.ORG_ID,
PV.VENDOR_ID,
PV.SEGMENT1 VENDOR_NUM,
PV.VENDOR_NAME,
PVS.VENDOR_SITE_ID,
PVS.VENDOR_SITE_CODE,
PHA.SEGMENT1,
PHA.CANCEL_FLAG,
PHA.START_DATE_ACTIVE,
PHA.END_DATE_ACTIVE,
PHA.AUTHORIZATION_STATUS,
PHA.CURRENCY_CODE,
PLA.LINE_NUM,
PLA.LINE_TYPE_ID,
PLT.LINE_TYPE,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ITEM,
PLA.CATEGORY_ID,
MSI.DESCRIPTION ITEM_DESCRIPTION,
MSITL.LONG_DESCRIPTION,
MCB.SEGMENT1 || '.' || MCB.SEGMENT2 || '.' || MCB.SEGMENT3 || '.' ||
MCB.SEGMENT4 || '.' || MCB.SEGMENT5 || '.' || MCB.SEGMENT6 CATECORY,
PLA.UNIT_PRICE,
PLA.UNIT_MEAS_LOOKUP_CODE,
PHA.REQUEST_ID,
PHA.CAT_ADMIN_AUTH_ENABLED_FLAG,
PHA.VENDOR_ORDER_NUM
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_TYPES PLT,
MTL_SYSTEM_ITEMS MSI,
MTL_SYSTEM_ITEMS_TL MSITL,
MTL_CATEGORIES_B MCB,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS
WHERE 1 = 1
AND MSI.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND MSITL.LANGUAGE = 'US' --USERENV('LANG')
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = 137
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND MCB.CATEGORY_ID = PLA.CATEGORY_ID
--AND PHA.SEGMENT1 = '7221543'
AND PHA.TYPE_LOOKUP_CODE = 'BLANKET'
AND PHA.AUTHORIZATION_STATUS = 'APPROVED' --已簽核結束
AND PHA.CANCEL_FLAG = 'N'
AND PLA.CANCEL_FLAG = 'N'
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PV.VENDOR_NAME = P_VENDOR_NAME
AND MSITL.LONG_DESCRIPTION = P_VENDOR_ITEM;
BEGIN
--DELETE PO.PO_HEADERS_INTERFACE;
--DELETE PO.PO_LINES_INTERFACE;
--DELETE PO.PO_INTERFACE_ERRORS;
FOR IMP_C IN IMPORT_PRICE LOOP
FOR C1 IN BPA(IMP_C.ATTR2, IMP_C.ATTR3) LOOP
UPDATE YOLIN_TEMP
SET ATTR5 = C1.PO_HEADER_ID,
ATTR6 = C1.PO_LINE_ID,
ATTR7 = C1.INVENTORY_ITEM_ID,
ATTR8 = C1.UNIT_PRICE,
ATTR9 = 'Y'
WHERE RPT_ID = IMP_C.RPT_ID
AND LINE_NO = IMP_C.LINE_NO;
INSERT INTO PO.PO_HEADERS_INTERFACE
(INTERFACE_HEADER_ID,
BATCH_ID,
ACTION,
ORG_ID,
DOCUMENT_TYPE_CODE,
--DOCUMENT_NUM, --OPTIONAL
VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE_ID,
--CREATION_DATE,
--CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
EFFECTIVE_DATE)
VALUES
(PO_HEADERS_INTERFACE_S.NEXTVAL, --INTERFACE_HEADER_ID
'20230329', --自定義
'UPDATE', --ACTION ORIGINAL、UPDATE、REPLACE
C1.ORG_ID, --OU_ID
C1.TYPE_LOOKUP_CODE, --DOCUMENT_TYPE_CODE
--'7090274',--C1.VENDOR_ORDER_NUM, --C1.VENDOR_ORDER_NUM, --DOCUMENT_NUM
C1.VENDOR_ID, --VENDOR_ID
C1.VENDOR_SITE_CODE, --VENDOR_SITE_CODE
C1.VENDOR_SITE_ID, --VENDOR_SITE_ID
--SYSDATE,
--2043,
SYSDATE,
0,
C1.PO_HEADER_ID,
SYSDATE);
INSERT INTO PO.PO_LINES_INTERFACE
(INTERFACE_LINE_ID,
INTERFACE_HEADER_ID,
ACTION,
ITEM,
ITEM_DESCRIPTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
--PROCESS_CODE,
--CREATION_DATE,
--CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
PO_LINE_ID)
VALUES
(PO_LINES_INTERFACE_S.NEXTVAL,
PO_HEADERS_INTERFACE_S.CURRVAL,
'UPDATE', --ACTION
C1.ITEM,
C1.ITEM_DESCRIPTION,
C1.UNIT_MEAS_LOOKUP_CODE, --UNIT_OF_MEASURE
IMP_C.ATTR4, --UNIT_PRICE
--'PENDING',
--SYSDATE,
--2043,
SYSDATE,
0,
C1.PO_HEADER_ID,
C1.PO_LINE_ID);
END LOOP;
END LOOP;
BEGIN
MO_GLOBAL.INIT('PO');
MO_GLOBAL.SET_POLICY_CONTEXT('S', 136); --OU_ID
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 0, --SYSDAMIN
RESP_ID => 50528, --PO SuperUser
RESP_APPL_ID => 201); --PO
FND_REQUEST.SET_ORG_ID(136);
--Call request Import Price Catalogs
P_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'PO',
PROGRAM => 'POXPDOI',
DESCRIPTION => NULL,
START_TIME => NULL,
SUB_REQUEST => NULL,
ARGUMENT1 => NULL, --Default Buyer
ARGUMENT2 => 'Blanket', --Document Type
ARGUMENT3 => NULL, --Document SubType
ARGUMENT4 => 'N', --Create or Update Items
ARGUMENT5 => 'N', --Create Sourcing Rules
ARGUMENT6 => 'APPROVED', --'INITIATE APPROVAL', --Approval Status
ARGUMENT7 => NULL, --Release Generation Method
ARGUMENT8 => '20230329', --Batch Id
ARGUMENT9 => 136, --Operating Unit
ARGUMENT10 => 'N', --Global Agreement
ARGUMENT11 => NULL, --Enable Sourcing Level
ARGUMENT12 => NULL, --Sourcing Level
ARGUMENT13 => NULL, --Inv Org Enable
ARGUMENT14 => NULL, --Inventory Organization
ARGUMENT15 => 'Y', --p_group_lines
ARGUMENT16 => 'N', --p_clm_flag
ARGUMENT17 => 5000, --p_batch_size
ARGUMENT18 => 'N' --p_gather_stats
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('REQUEST ID:' || P_REQUEST_ID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE || '-' || SQLERRM);
END;
END;
更新檢查錯誤訊息
SELECT PIE.ERROR_MESSAGE,
PHA.SEGMENT1 BPA_NUMBER,
PLA.LINE_NUM,
MSI.SEGMENT1 ITEM,
PLA.UNIT_PRICE CURRENT_PRICE,
PLAI.UNIT_PRICE INTERFACE_PRICE,
PLA.PO_HEADER_ID,
PLA.PO_LINE_ID
FROM PO_HEADERS_INTERFACE PHAI,
PO_LINES_INTERFACE PLAI,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS MSI,
PO_INTERFACE_ERRORS PIE
WHERE 1 = 1
AND PHAI.INTERFACE_HEADER_ID = PIE.INTERFACE_HEADER_ID
AND PLAI.INTERFACE_LINE_ID = PIE.INTERFACE_LINE_ID
AND PLAI.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PLAI.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = 137 --ORG_ID
AND PIE.BATCH_ID = '20230406'; --BATCH_ID
沒有留言:
張貼留言