CREATE OR REPLACE PACKAGE MEW_A1_AUTO_RECEIVING_PKG IS
PROCEDURE MAIN(RETCODE OUT NUMBER
,ERRBUF OUT VARCHAR
,P_CONTROL_ID NUMBER
,P_FILE_NAME VARCHAR
,P_MAIL_TO VARCHAR);
END;
/
CREATE OR REPLACE PACKAGE BODY MEW_A1_AUTO_RECEIVING_PKG IS
PROCEDURE MAIN(RETCODE OUT NUMBER
,ERRBUF OUT VARCHAR
,P_CONTROL_ID NUMBER
,P_FILE_NAME VARCHAR
,P_MAIL_TO VARCHAR) IS
CURSOR C1 IS
SELECT RHI.HEADER_INTERFACE_ID
,RTI.INTERFACE_TRANSACTION_ID
,RTI.TO_ORGANIZATION_CODE
FROM MEW_PO_EDI_INBOUND MPI
,RCV_HEADERS_INTERFACE RHI
,RCV_TRANSACTIONS_INTERFACE RTI
WHERE MPI.DATA_ID = 'A1'
AND MPI.IMPORT_FLAG = 'Y'
AND NVL(MPI.STATUS
,'E') <> 'E'
AND MPI.INVOICE_NUMBER = RHI.INVOICE_NUM
AND RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
AND MPI.PO_LINE_LOCATIONS_ID = RTI.PO_LINE_LOCATION_ID
-- AND MPI.CONTROL_ID = 181749;
AND MPI.CONTROL_ID = P_CONTROL_ID;
V_GROUP_ID NUMBER;
V_REQUEST_ID NUMBER;
V_WAIT_FLAG BOOLEAN;
X_PHASE VARCHAR2(100);
X_STATUS VARCHAR2(100);
X_DEV_PHASE VARCHAR2(100);
X_DEV_STATUS VARCHAR2(100);
X_MESSAGE VARCHAR2(100);
CNT NUMBER;
V_MAIL_BODY VARCHAR2(32767);
BEGIN
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;
IF P_CONTROL_ID IS NOT NULL
THEN
FOR R1 IN C1
LOOP
UPDATE RCV_HEADERS_INTERFACE RHI
SET RHI.EXPECTED_RECEIPT_DATE = SYSDATE
,RHI.PROCESSING_STATUS_CODE = 'PENDING'
,RHI.GROUP_ID = V_GROUP_ID
WHERE RHI.HEADER_INTERFACE_ID = R1.HEADER_INTERFACE_ID;
UPDATE RCV_TRANSACTIONS_INTERFACE RTI
SET RTI.EXPECTED_RECEIPT_DATE = SYSDATE
,RTI.PROCESSING_STATUS_CODE = 'PENDING'
,RTI.TRANSACTION_STATUS_CODE = 'PENDING'
,RTI.GROUP_ID = V_GROUP_ID
,RTI.SUBINVENTORY =
(SELECT SUBINV
FROM (SELECT MSV.SEGMENT3 SUBINV
FROM MEW_SUB_QUICK_TYPE_VALUE MSV
,MEW_SUB_QUICK_TYPE MST
WHERE MST.LOOKUP_TYPE_ID = MSV.LOOKUP_TYPE_ID
AND MST.LOOKUP_TYPE = 'PEWC:MEW INTRANSIT SUBINV'
AND MSV.SEGMENT1 = R1.TO_ORGANIZATION_CODE
ORDER BY MSV.SEGMENT2)
WHERE ROWNUM = 1)
WHERE RTI.INTERFACE_TRANSACTION_ID = R1.INTERFACE_TRANSACTION_ID;
END LOOP;
ELSE
FOR R1 IN (SELECT RHI.HEADER_INTERFACE_ID
,RTI.INTERFACE_TRANSACTION_ID
,RTI.TO_ORGANIZATION_CODE
FROM MEW_PO_EDI_INBOUND MPI
,RCV_HEADERS_INTERFACE RHI
,RCV_TRANSACTIONS_INTERFACE RTI
,(SELECT REQUEST_ID
FROM (SELECT REQUEST_ID
FROM FND_CONC_REQ_SUMMARY_V FC
WHERE USER_CONCURRENT_PROGRAM_NAME =
'MEW Import of Inbound EDI Po''s'
AND TRUNC(REQUEST_DATE) < SYSDATE
AND SUBSTR(FC.ARGUMENT_TEXT
,1
,INSTR(FC.ARGUMENT_TEXT
,',') - 1) = P_FILE_NAME
ORDER BY REQUEST_ID DESC)
WHERE ROWNUM < 3) REQ
WHERE MPI.DATA_ID = 'A1'
AND MPI.IMPORT_FLAG = 'Y'
AND NVL(MPI.STATUS
,'E') <> 'E'
AND MPI.INVOICE_NUMBER = RHI.INVOICE_NUM
AND RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
AND MPI.PO_LINE_LOCATIONS_ID = RTI.PO_LINE_LOCATION_ID
AND MPI.REQUEST_ID = REQ.REQUEST_ID)
LOOP
UPDATE RCV_HEADERS_INTERFACE RHI
SET RHI.EXPECTED_RECEIPT_DATE = SYSDATE
,RHI.PROCESSING_STATUS_CODE = 'PENDING'
,RHI.GROUP_ID = V_GROUP_ID
WHERE RHI.HEADER_INTERFACE_ID = R1.HEADER_INTERFACE_ID;
UPDATE RCV_TRANSACTIONS_INTERFACE RTI
SET RTI.EXPECTED_RECEIPT_DATE = SYSDATE
,RTI.PROCESSING_STATUS_CODE = 'PENDING'
,RTI.TRANSACTION_STATUS_CODE = 'PENDING'
,RTI.GROUP_ID = V_GROUP_ID
,RTI.SUBINVENTORY =
(SELECT SUBINV
FROM (SELECT MSV.SEGMENT3 SUBINV
FROM MEW_SUB_QUICK_TYPE_VALUE MSV
,MEW_SUB_QUICK_TYPE MST
WHERE MST.LOOKUP_TYPE_ID = MSV.LOOKUP_TYPE_ID
AND MST.LOOKUP_TYPE = 'PEWC:MEW INTRANSIT SUBINV'
AND MSV.SEGMENT1 = R1.TO_ORGANIZATION_CODE
ORDER BY MSV.SEGMENT2)
WHERE ROWNUM = 1)
WHERE RTI.INTERFACE_TRANSACTION_ID = R1.INTERFACE_TRANSACTION_ID;
END LOOP;
END IF;
COMMIT;
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('PO'
,'RVCTP'
,'Receiving Transaction Processor'
,NULL
,FALSE
,'BATCH'
,V_GROUP_ID);
COMMIT;
IF V_REQUEST_ID > 0
THEN
V_WAIT_FLAG := FND_CONCURRENT.WAIT_FOR_REQUEST(V_REQUEST_ID
,60
,0
,X_PHASE
,X_STATUS
,X_DEV_PHASE
,X_DEV_STATUS
,X_MESSAGE);
IF X_DEV_PHASE = 'COMPLETE' AND
X_DEV_STATUS = 'NORMAL'
THEN
V_MAIL_BODY := '用户您好，以下是A1自动接收未成功的记录，请处理.' || CHR(10) ||
'-------------------------------------------------------------------------------------------------------------------------------' ||
CHR(10) ||
'OU VENDOR INVOICE# PO# LINE# SHIP# QTY ERROR_MESSAGE';
SELECT COUNT(*)
INTO CNT
FROM RCV_HEADERS_INTERFACE RHI
,RCV_TRANSACTIONS_INTERFACE RTI
,PO_INTERFACE_ERRORS PIE
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND RTI.INTERFACE_TRANSACTION_ID = PIE.INTERFACE_TRANSACTION_ID
AND RHI.GROUP_ID = V_GROUP_ID;
IF NVL(CNT
,0) > 0
THEN
FOR R1 IN (SELECT HOU.ORGANIZATION_CODE NAME
,PV.SEGMENT1 VENDOR_NUMBER
,RHA.INVOICE_NUM
,PHA.SEGMENT1 PO_NUMBER
,PLA.LINE_NUM
,PLLA.SHIPMENT_NUM
,RTI.QUANTITY
,PIE.ERROR_MESSAGE_NAME
,PIE.ERROR_MESSAGE
FROM RCV_HEADERS_INTERFACE RHA
,RCV_TRANSACTIONS_INTERFACE RTI
,PO_INTERFACE_ERRORS PIE
,PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,PO_LINE_LOCATIONS_ALL PLLA
,PO_VENDORS PV
,ORG_ORGANIZATION_DEFINITIONS HOU
WHERE RHA.HEADER_INTERFACE_ID =
RTI.HEADER_INTERFACE_ID
AND RTI.INTERFACE_TRANSACTION_ID =
PIE.INTERFACE_LINE_ID
AND RTI.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.ORG_ID = HOU.OPERATING_UNIT
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND RTI.PO_LINE_ID = PLA.PO_LINE_ID
AND RTI.PO_LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
AND RTI.GROUP_ID = V_GROUP_ID)
LOOP
V_MAIL_BODY := V_MAIL_BODY || CHR(10) ||
RPAD(R1.NAME
,10
,CHR(32)) || RPAD(R1.VENDOR_NUMBER
,15
,CHR(32)) ||
RPAD(NVL(R1.INVOICE_NUM
,CHR(32))
,15
,CHR(32)) || RPAD(R1.PO_NUMBER
,15
,CHR(32)) ||
RPAD(R1.LINE_NUM
,6
,CHR(32)) || RPAD(R1.SHIPMENT_NUM
,5
,CHR(32)) ||
LPAD(R1.QUANTITY
,10
,CHR(32)) || ' ' ||
RPAD(SUBSTR(R1.ERROR_MESSAGE
,1
,80)
,80
,CHR(32));
END LOOP;
END IF;
IF P_CONTROL_ID IS NOT NULL
THEN
FOR R1 IN (SELECT HOU.ORGANIZATION_CODE NAME
,PV.SEGMENT1 VENDOR_NUMBER
,MPI.INVOICE_NUMBER
,MPI.PO_NUMBER
,PLA.LINE_NUM
,PLLA.SHIPMENT_NUM
,MPI.QUANTITY
,MPI.MESSAGE
FROM MEW_PO_EDI_INBOUND MPI
,PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,PO_LINE_LOCATIONS_ALL PLLA
,ORG_ORGANIZATION_DEFINITIONS HOU
,PO_VENDORS PV
WHERE MPI.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.ORG_ID = HOU.OPERATING_UNIT
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND MPI.PO_LINE_ID = PLA.PO_LINE_ID
AND MPI.PO_LINE_LOCATIONS_ID = PLLA.LINE_LOCATION_ID
AND MPI.STATUS = 'E'
AND MPI.DATA_ID = 'A1'
AND MPI.CONTROL_ID = P_CONTROL_ID)
LOOP
V_MAIL_BODY := V_MAIL_BODY || CHR(10) ||
RPAD(R1.NAME
,10
,CHR(32)) || RPAD(R1.VENDOR_NUMBER
,15
,CHR(32)) ||
RPAD(NVL(R1.INVOICE_NUMBER
,CHR(32))
,15
,CHR(32)) || RPAD(R1.PO_NUMBER
,15
,CHR(32)) ||
RPAD(R1.LINE_NUM
,6
,CHR(32)) || RPAD(R1.SHIPMENT_NUM
,5
,CHR(32)) ||
LPAD(R1.QUANTITY
,10
,CHR(32)) || ' ' ||
RPAD(SUBSTR(R1.MESSAGE
,1
,80)
,80
,CHR(32));
END LOOP;
ELSE
FOR R1 IN (SELECT HOU.ORGANIZATION_CODE NAME
,PV.SEGMENT1 VENDOR_NUMBER
,MPI.INVOICE_NUMBER
,MPI.PO_NUMBER
,PLA.LINE_NUM
,PLLA.SHIPMENT_NUM
,MPI.QUANTITY
,MPI.MESSAGE
FROM MEW_PO_EDI_INBOUND MPI
,PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,PO_LINE_LOCATIONS_ALL PLLA
,ORG_ORGANIZATION_DEFINITIONS HOU
,PO_VENDORS PV
,(SELECT REQUEST_ID
FROM (SELECT REQUEST_ID
FROM FND_CONC_REQ_SUMMARY_V FC
WHERE USER_CONCURRENT_PROGRAM_NAME =
'MEW Import of Inbound EDI Po''s'
AND TRUNC(REQUEST_DATE) < SYSDATE
AND SUBSTR(FC.ARGUMENT_TEXT
,1
,INSTR(FC.ARGUMENT_TEXT
,',') - 1) =
P_FILE_NAME
ORDER BY REQUEST_ID DESC)
WHERE ROWNUM < 3) REQ
WHERE MPI.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.ORG_ID = HOU.OPERATING_UNIT
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND MPI.PO_LINE_ID = PLA.PO_LINE_ID
AND MPI.PO_LINE_LOCATIONS_ID = PLLA.LINE_LOCATION_ID
AND MPI.STATUS = 'E'
AND MPI.DATA_ID = 'A1'
AND REQ.REQUEST_ID = MPI.REQUEST_ID)
LOOP
V_MAIL_BODY := V_MAIL_BODY || CHR(10) ||
RPAD(R1.NAME
,10
,CHR(32)) || RPAD(R1.VENDOR_NUMBER
,15
,CHR(32)) ||
RPAD(NVL(R1.INVOICE_NUMBER
,CHR(32))
,15
,CHR(32)) || RPAD(R1.PO_NUMBER
,15
,CHR(32)) ||
RPAD(R1.LINE_NUM
,6
,CHR(32)) || RPAD(R1.SHIPMENT_NUM
,5
,CHR(32)) ||
LPAD(R1.QUANTITY
,10
,CHR(32)) || ' ' ||
RPAD(SUBSTR(R1.MESSAGE
,1
,80)
,80
,CHR(32));
END LOOP;
END IF;
UTL_MAIL.SEND(SENDER => 'EBS@cn.pewg.panasonic.com'
,RECIPIENTS => P_MAIL_TO
,MESSAGE => V_MAIL_BODY
,SUBJECT => 'MEW EDI A1 Auto Receiving Exceptions'
,MIME_TYPE => 'text/plain;charset=ZHS16GBK');
FND_FILE.PUT_LINE(FND_FILE.LOG
,V_MAIL_BODY);
END IF;
END IF;
END;
END;
/
457
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
