AS
PROCEDURE HDG_THREAD_PRC (ERROR_MSG_TXT OUT VARCHAR2,
RETURN_STATUS OUT VARCHAR2)
IS
CURSOR LOOKUP_CUR
IS
SELECT MEANING THREAD_ID
FROM HDG.HDG_LOOKUP_TBL
WHERE LOOKUP_TYPE = 'RMA_VALIDATION'
AND LOOKUP_CODE = 'MAXIMUM THREADS';
CURSOR HEADER_CUR
IS
SELECT /*+ RULE */
H.ADJUSTMENT_HEADER_ID, COUNT (*)
FROM HDG.HDG_RET_ADJ_LINES_TBL L, HDG.HDG_RET_ADJ_HEADER_TBL H
WHERE H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.RECORD_INTERFACED != 'Y'
AND NVL (H.DUPLICATE_FLAG, 'N') != 'Y'
AND H.CUSTOMER_ID IS NOT NULL
AND L.LINE_INTERFACED != 'Y'
AND ( ( (L.VALID_STATUS_FLAG IS NULL)
OR ( NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND ( ( OVERRIDE_FLAG = 'Y'
AND APPROVAL_STATUS = 'APPROVED')
OR USER_REJECTED_FLAG = 'Y'
OR CORRECTED_FLAG = 'Y')))
OR INVALID_REASON_CODE = 'HDR-DTL-MISMATCH')
GROUP BY H.ADJUSTMENT_HEADER_ID;
V_MAX_THREADS NUMBER;
V_NO_HDRS NUMBER;
V_COUNT NUMBER;
V_THREAD_NR NUMBER;
V_REQUEST_ID NUMBER;
V_NEXT_TBL_ROW NUMBER;
V_ROAR_THREAD_ID NUMBER;
V_DEV_STATUS VARCHAR2 (100);
TYPE REQUESTS_RECTYPE IS RECORD (REQUEST_ID NUMBER);
REQUESTS_REC REQUESTS_RECTYPE;
TYPE REQUESTS_TBLTYP IS TABLE OF REQUESTS_RECTYPE
INDEX BY BINARY_INTEGER;
REQUESTS_TBL REQUESTS_TBLTYP;
BEGIN
REQUESTS_TBL.DELETE;
V_USER_ID := FND_GLOBAL.USER_ID;
-- V_FILE_HDL := HDG_RMAVAL_PCK.HDG_OPEN_OUT_FILE_FNC
-- (V_USER_ID,FND_GLOBAL.CONC_REQUEST_ID);
CMG_UTILITY_PCK.WRITE_OUTPUT ('CLEANING UP INVALID CUSTOMERS');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CLEANING UP INVALID CUSTOMERS');
HDG_VALIDATE_CUSTOMERS_PRC;
/*
NEXT FIND ALL HEADERS THAT HAVE VALID CUSTOMER AND BILL-TO IDS, BUT ARE
MISSING SHIP-TO IDS. THESE ARE MOST LIKELY CUSTOMERS THAT HAVE MULTIPLE
SHIP-TOS FOR WHICH THE LOAD ROUTINE COULD NOT ASSIGN A REASONABLE VALUE
*/
HDG_AFFIX_LIKELY_SHIP_TOS_PRC;
/* IF ANY OF THE HEADER IS MISSING CUSTOMER_REF_NR OR BLANK THEN A
ROAR SEQUENCE NUMBER IS ASSIGNED
*/
HDG_FIX_MISSING_CUST_REF_PRC;
/* REJECTED ALL THE WEEKLY CUSTOMERS TALLYS SO THAT THEY WILL BE PICKED UP
IN THE NEXT VALIDATION RUN
*/
/*
NEXT FIND ALL RETURN LINES THAT HAVE BEEN ASSIGNED TO THE
MASTER WAREHOUSE AND RE-ASSIGN THEM APPROPRIATELY TO THE
HDG OR THE EN WAREHOUSE.
*/
HDG_SHARED_CUST_WAREHOUSE_PRC;
/* FIX ALL THE BAD PRODUCTS AND SET INVALID_REASON_CODE TO BAD ISSUE IF
THEY ARE FOUND IN INVENTORY */
HDG_FIX_BAD_PRODUCTS_PRC;
/* REJECT ALL THE AVON PUBLISHED TITLES IE HARPER COLLINS
*/
-------------------HDG_AUTO_REJECT_AVON_PRC;
HDG_FIX_ADD_ON_CODES_PRC;
/* AS PER DAVID/TINA/CLAIRE EMAIL REQUEST ON 20-APR-2002 THE BIPADS
HAS BEEN MAPPED TO REFLECT ITEM SETUP.INV_ITEM ID REMAINS SAME */
HDG_BIPAD_FIX_PRC;
/* AS PER CHRIS ARGENTIERI REQUEST SOME OF THE CUSTOMERS SHOULD NOT RETURN
TVG GUIDE PRODUCTS,THEY SHOULD BE REJECTED AS 'NON-RETURNABLE
HERE ARE THE FOLLOWING CUSTOMERS AS PER 07/30/02 EMAIL FROM DAVID J
M-IMAX THTR-NAIGARA FALLS
M-NATL GEOGRAPHIC-VICTORIA
M-IN FLIGT NEW PRS-VALLEY STREAM
*/
HDG_REJ_TVG_CUSTOMER_PRC;
/* ANY RETURNS COMING WITH OLD BIPADS ARE MAPPED TO NEW BIPADS */
HDG_RET_BIPAD_MAPPING_PRC;
CMG_UTILITY_PCK.WRITE_OUTPUT ('BEFORE CALLING THE ISSUE MAPPING PROC');
HDG_RET_ISSUE_MAPPING_PRC;
HDG_RET_ISSUE_ADDON_FIX_PRC;
/* INORDER TO PREVENT THE HEADER DETAIL MISMATCH ERROR THE TOTAL COPIES ON
LINE LEVEL ARE UPDATED WITH HEADER */
HDG_FIX_HDR_DTL_MISMATCH_PRC;
OPEN LOOKUP_CUR;
FETCH LOOKUP_CUR INTO V_MAX_THREADS;
IF LOOKUP_CUR%NOTFOUND
THEN
V_MAX_THREADS := 1;
END IF;
CLOSE LOOKUP_CUR;
V_COUNT := 1;
V_ROAR_THREAD_ID := 1;
FOR HDR_REC IN HEADER_CUR
LOOP
IF V_COUNT > V_MAX_THREADS
THEN
V_COUNT := 1;
V_ROAR_THREAD_ID := 1;
END IF;
UPDATE HDG_RET_ADJ_HEADER_TBL
SET ROAR_THREAD_ID = V_ROAR_THREAD_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = V_USER_ID
WHERE ADJUSTMENT_HEADER_ID = HDR_REC.ADJUSTMENT_HEADER_ID;
COMMIT;
V_COUNT := V_COUNT + 1;
V_ROAR_THREAD_ID := V_ROAR_THREAD_ID + 1;
END LOOP;
FOR V_THREAD_COUNT IN 1 .. V_MAX_THREADS
LOOP
V_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST (
'HDG',
'HDGRT_RMA_VALIDATIONS',
'RMA VALIDATIONS FOR ELECTRONIC AND MANUAL RETURNS',
'',
FALSE,
V_THREAD_COUNT);
V_NEXT_TBL_ROW := REQUESTS_TBL.COUNT + 1;
REQUESTS_TBL (V_NEXT_TBL_ROW).REQUEST_ID := V_REQUEST_ID;
END LOOP;
COMMIT;
FOR V_ROWS_IN_TBL IN REQUESTS_TBL.FIRST .. REQUESTS_TBL.COUNT
LOOP
IF REQUESTS_TBL (V_ROWS_IN_TBL).REQUEST_ID <> 0
THEN
HDG_SUBMIT_REQUEST_STATUS_PRC (
REQUESTS_TBL (V_ROWS_IN_TBL).REQUEST_ID);
END IF;
END LOOP;
CMG_UTILITY_PCK.WRITE_OUTPUT (
'BEFORE CALLING THE GEN_ORDERTYPE_ASSIGN_PRC');
GEN_ORDERTYPE_ASSIGN_PRC; --Genera Foreign Accounts SCR 8385
CMG_UTILITY_PCK.WRITE_OUTPUT (
'BEFORE CALLING THE GEN_DELETE_INVALID_HEADERS_PRC');
GEN_DELETE_INVALID_HEADERS_PRC; --Genera Foreign Accounts SCR 8385
/* REJECT ALL THE BOOK TALLIES WHERE THE SUM OF QUANITY OF LINES
IS LESS THAN HEADERAUTOREJECT LIMIT AND NO OF LINES LESS THAN
THE LINE LEVEL AUTOREJECTLIMIT
*/
END HDG_THREAD_PRC;
PROCEDURE HDG_SUBMIT_REQUEST_STATUS_PRC (P_REQUEST_ID IN NUMBER)
IS
V_REQ_ID NUMBER;
V_CALL_STATUS BOOLEAN;
V_PHASE VARCHAR2 (100);
V_STATUS VARCHAR2 (100);
V_DEV_PHASE VARCHAR2 (100);
V_DEV_STATUS VARCHAR2 (100);
V_MESSAGE VARCHAR2 (100);
BEGIN
V_REQ_ID := P_REQUEST_ID;
LOOP
V_CALL_STATUS :=
FND_CONCURRENT.GET_REQUEST_STATUS (V_REQ_ID,
NULL,
NULL,
V_PHASE,
V_STATUS,
V_DEV_PHASE,
V_DEV_STATUS,
V_MESSAGE);
EXIT WHEN V_DEV_PHASE = 'COMPLETE' OR V_PHASE = 'COMPLETED';
END LOOP;
END HDG_SUBMIT_REQUEST_STATUS_PRC;
PROCEDURE HDG_VALIDATE_CUSTOMERS_PRC
IS
CURSOR INVALID_CUST_CUR
IS
SELECT ADJUSTMENT_HEADER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CUSTOMER_REF_NR,
TALLY_NR,
ADJUSTMENT_FLAG,
RECORD_INTERFACED,
ACCOUNT_NR,
IPDA_MAILBOX_NR,
SOURCE,
TOTAL_COPIES,
CUSTOMER_ID,
CUSTOMER_NAME,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
RETURN_DATE,
PROCESSED_DATE,
EMPLOYEE_ASSIGNED_TO,
ORDER_CATEGORY,
ORDER_TYPE_ID,
VALID_STATUS_FLAG,
DUPLICATE_FLAG,
STORE_NR,
PRICE_LIST_ID,
ORIGINAL_ORDER_HEADER_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL
WHERE NVL (VALID_STATUS_FLAG, 'N') = 'N'
AND NVL (RECORD_INTERFACED, 'N') = 'N'
AND CUSTOMER_ID IS NULL
FOR UPDATE OF ADJUSTMENT_HEADER_ID NOWAIT;
CURSOR ITEM_TYPE_CUR (
P_HEADER_ID IN NUMBER)
IS
SELECT ITEM_TYPE
FROM HDG.HDG_RET_ADJ_LINES_TBL L, MTL_SYSTEM_ITEMS M
WHERE L.ADJUSTMENT_HEADER_ID = P_HEADER_ID
AND L.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND L.WAREHOUSE_ID = M.ORGANIZATION_ID
GROUP BY ITEM_TYPE;
CURSOR LINE_CUR (
P_HEADER_ID IN NUMBER,
P_ITEM_TYPE IN VARCHAR2)
IS
SELECT ADJUSTMENT_HEADER_ID, ADJUSTMENT_LINE_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L, MTL_SYSTEM_ITEMS M
WHERE L.ADJUSTMENT_HEADER_ID = P_HEADER_ID
AND L.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND L.WAREHOUSE_ID = M.ORGANIZATION_ID
AND M.ITEM_TYPE = P_ITEM_TYPE;
CURSOR NEW_HDRS_4TALLY_CUR (
P_TALLY_NR IN NUMBER,
P_EXCLUDE_ADJ_HDR_ID IN NUMBER)
IS
SELECT ADJUSTMENT_HEADER_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL
WHERE TALLY_NR = P_TALLY_NR
AND ADJUSTMENT_HEADER_ID <> P_EXCLUDE_ADJ_HDR_ID;
V_THIS_ITEM_TYPE VARCHAR2 (20);
V_ITEM_TYPE_POS NUMBER;
V_NEW_CUSTOMER_ID NUMBER;
V_PRIOR_CUSTOMER_ID NUMBER;
V_NEW_CUSTOMER_NM VARCHAR2 (50);
V_NEW_BILL_SITE_ID NUMBER;
V_NEW_SHIP_SITE_ID NUMBER;
V_ADJ_HDR_ID NUMBER;
V_SPLIT_HDR_ID NUMBER;
V_ONE_OR_MORE_BAD_ITEMS BOOLEAN;
V_ORACLE_WHOUSE_ID NUMBER;
V_ORACLE_ITEM_ID NUMBER;
V_ORACLE_ITEM_NBR VARCHAR2 (40);
V_NEW_HDR_SOURCE VARCHAR2 (4);
V_APPORTIONED_COPIES NUMBER;
V_NEW_ORDER_TYPE_ID NUMBER;
V_LAST_ITEM_TYPE VARCHAR2 (20);
V_BOOK_ITEM_TYPE BOOLEAN DEFAULT FALSE;
BEGIN
CMG_UTILITY_PCK.WRITE_OUTPUT (
'FOLLOWING HEADERS HAD INVALID CUSTOMERS');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'FOLLOWING HEADERS HAD INVALID CUSTOMERS',
-- 1);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- '***************************************',
-- 1);
FOR HDR_REC IN INVALID_CUST_CUR
LOOP
CMG_UTILITY_PCK.WRITE_OUTPUT (
'IPDA MAILBOX # : ' || HDR_REC.IPDA_MAILBOX_NR);
CMG_UTILITY_PCK.WRITE_OUTPUT (
'LEGACY ACCOUNT # : ' || HDR_REC.ACCOUNT_NR);
CMG_UTILITY_PCK.WRITE_OUTPUT (
'CHECKING IF MULTIPLE ITEM TYPES EXIST');
CMG_UTILITY_PCK.WRITE_OUTPUT (
' HEADER ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'IPDA MAILBOX # : ' || HDR_REC.IPDA_MAILBOX_NR,
-- 2);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'LEGACY ACCOUNT # : ' || HDR_REC.ACCOUNT_NR,
-- 2);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CHECKING IF MULTIPLE ITEM TYPES EXIST',
-- 2);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC(V_FILE_HDL,' HEADER '||
-- TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
/*
THE CUSTOMER COULD BE INVALID/NULL BECAUSE MULTIPLE ITEM TYPES
WERE FOUND FOR ONE HEADER. IN THIS CASE THE HEADER NEEDS TO BE
SPLIT, ONCE EACH FOR DISTINCT ITEM TYPE FOUND
*/
HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL.DELETE;
V_ONE_OR_MORE_BAD_ITEMS := FALSE;
/*
BEFORE STARTING TO FIX EACH HEADER ISSUE A SAVEPOINT. IF ANY
INVALID ITEM TYPES ARE FOUND WITHIN THE HEADER, ROLLBACK TO THIS.
*/
-- SAVEPOINT PRE_BAD_ITMTYPES;
V_LAST_ITEM_TYPE := NULL;
/* FOR ADJLIN_REC IN SELECT_LINE (HDR_REC.ADJUSTMENT_HEADER_ID, 'YES') LOOP
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'BIPAD : ' || ADJLIN_REC.BIPAD,
-- 2);
*/
FOR ITEM_TYPE_REC IN ITEM_TYPE_CUR (HDR_REC.ADJUSTMENT_HEADER_ID)
LOOP
V_THIS_ITEM_TYPE := ITEM_TYPE_REC.ITEM_TYPE;
/*
V_THIS_ITEM_TYPE := HDG_GET_ITEM_TYPE_FNC (
ADJLIN_REC.WAREHOUSE_ID,
ADJLIN_REC.INVENTORY_ITEM_ID);
*/
/*
HDG_RMAVAL_PCK.HDG_GET_ITEM_TYPE_PRC (
NVL(RTRIM(ADJLIN_REC.BIPAD), ADJLIN_REC.ISBN),
V_ORACLE_WHOUSE_ID,
V_ORACLE_ITEM_ID,
V_THIS_ITEM_TYPE,
V_ORACLE_ITEM_NBR);
*/
IF V_THIS_ITEM_TYPE = 'INVALID'
THEN
V_ONE_OR_MORE_BAD_ITEMS := TRUE;
CMG_UTILITY_PCK.WRITE_OUTPUT ('INVALID ITEM TYPE DETECTED');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'INVALID ITEM TYPE DETECTED',
-- 2);
END IF;
/*
ONCE AN ITEM TYPE HAS BEEN DERIVED, DETERMINE WHICH BUSINESS UNIT
TO USE AND THEREBY DERIVE THE CUSTOMER INFORMATION.
*/
IF V_THIS_ITEM_TYPE != 'INVALID'
THEN
IF NVL (V_LAST_ITEM_TYPE, 'FIRST ITEM') <> V_THIS_ITEM_TYPE
THEN
CMG_UTILITY_PCK.WRITE_OUTPUT (
'BEFORE ORACLE CUSTOMER '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC(V_FILE_HDL,
--'BEFORE ORACLE CUSTOMER '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
HDG_RMAVAL_PCK.HDG_GET_ORACLE_CUSTOMER_PRC (
V_THIS_ITEM_TYPE,
HDR_REC.ACCOUNT_NR,
V_NEW_CUSTOMER_ID,
V_NEW_CUSTOMER_NM,
V_NEW_BILL_SITE_ID,
V_NEW_SHIP_SITE_ID,
V_NEW_HDR_SOURCE,
V_NEW_ORDER_TYPE_ID,
HDR_REC.IPDA_MAILBOX_NR);
V_LAST_ITEM_TYPE := V_THIS_ITEM_TYPE;
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC(V_FILE_HDL,
--'AFTER ORACLE CUSTOMER '|| TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
CMG_UTILITY_PCK.WRITE_OUTPUT (
'AFTER ORACLE CUSTOMER '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END IF;
IF V_NEW_CUSTOMER_ID <> NVL (V_PRIOR_CUSTOMER_ID, 0)
THEN
V_PRIOR_CUSTOMER_ID := V_NEW_CUSTOMER_ID;
CMG_UTILITY_PCK.WRITE_OUTPUT (
'CUSTOMER ID ASSIGNED : ' || TO_CHAR (V_NEW_CUSTOMER_ID));
CMG_UTILITY_PCK.WRITE_OUTPUT (
'CUSTOMER NAME : ' || V_NEW_CUSTOMER_NM);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CUSTOMER ID ASSIGNED : ' ||
-- TO_CHAR(V_NEW_CUSTOMER_ID),
-- 3);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CUSTOMER NAME : ' || V_NEW_CUSTOMER_NM,
-- 3);
END IF;
/*
THE ITEM WOULD HAVE LOADED AS INVALID WITH NULL INVENTORY_ITEM_ID
AND NULL WAREHOUSE_ID (BASIC ITEM VALIDATION IS DRIVEN BY THE SOURCE
HDG OR EN TO LOCATE THE ITEM IN CORRECT WAREHOUSE AND SOURCE IN TURN
IS DRIVEN BY CUSTOMER).IF CURRENT VALID_STATUS_FLAG ON HEADER IS 'N'
BECAUSE OF BAD PRODUCT REASON CODE, RESET STATUS AND REJECT REASON
CODE FOR CURRENT ITEM TO NULL, AS LONG AS ITEM TYPE RETURNED FROM
HDG_GET_ITEM_TYPE_PRC PROCEDURE ABOVE IS NOT INVALID
ADDITIONALLY, UPDATE THE LINES TABLE WITH THE NEW ORACLE ITEM INFO
ONLY IF IT DOES NOT ALREADY EXIST.
WHY ONLY IF IT DOES NOT ALREADY EXIST? EVEN IF THE NEWLY VALIDATED
ITEM POINTS TO ANOTHER ID, WOULD THAT NOT BE THE ITEM ID TO USE?
MODIFIED 03/03/99 BY RTHARAKA TO UPDATE THE ITEM INFORMATION
WHETHER OR NOT THE ITEM ID AND WAREHOUSE ID ALREADY EXIST.
*/
END IF;
/*
SCAN FOR THIS ITEM TYPE IN THE DISTINCT ITEM TYPES PL/SQL TABLE.
IF IT IS NOT FOUND, IMPLIES THE NEED FOR A SPLIT HEADER, SINCE
A NEW ITEM TYPE WAS DETECTED. IF ALREADY FOUND, SET THE HEADER ID
TO THE VALUE POINTED TO BY THAT ITEM TYPE
*/
HDG_RMAVAL_PCK.HDG_SCAN_ITEM_TYPE_PRC (V_THIS_ITEM_TYPE,
V_ITEM_TYPE_POS,
V_ADJ_HDR_ID);
IF V_ITEM_TYPE_POS = 0
THEN
IF HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL.COUNT = 0
THEN
/*
THIS IS THE FIRST ITEM LINE PROCESSED, SO LEAVE ASSOCIATION TO
TO CURRENT HEADER AS IS. ALL SUBSEQUENT LINES THAT HAVE THIS
ITEM TYPE WILL BELONG TO THIS HEADER
*/
IF V_THIS_ITEM_TYPE != 'INVALID'
THEN
HDG_RMAVAL_PCK.HDG_INSERT_ITEM_TYPE_PRC (
V_THIS_ITEM_TYPE,
HDR_REC.ADJUSTMENT_HEADER_ID);
UPDATE HDG_RET_ADJ_HEADER_TBL
SET CUSTOMER_ID = V_NEW_CUSTOMER_ID,
CUSTOMER_NAME = V_NEW_CUSTOMER_NM,
BILL_TO_SITE_USE_ID = V_NEW_BILL_SITE_ID,
SHIP_TO_SITE_USE_ID = V_NEW_SHIP_SITE_ID,
SOURCE = NVL (V_NEW_HDR_SOURCE, 'NO SOURCE'),
ORDER_TYPE_ID = V_NEW_ORDER_TYPE_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = V_USER_ID
WHERE CURRENT OF INVALID_CUST_CUR;
END IF;
ELSE
/*
CREATE A SPLIT HEADER AND INSERT THAT HEADER ID (ALONGWITH THE
CURRENT ITEM TYPE) INTO THE PL/SQL TABLE
*/
NULL;
/*
IF V_THIS_ITEM_TYPE != 'INVALID' THEN
V_SPLIT_HDR_ID := HDG_RMAVAL_PCK.HDG_SPLIT_TO_BULK_HDR_FNC (
HDR_REC.ADJUSTMENT_HEADER_ID,
V_NEW_SHIP_SITE_ID,
V_NEW_BILL_SITE_ID,
V_NEW_CUSTOMER_ID,
V_NEW_CUSTOMER_NM,
V_NEW_ORDER_TYPE_ID);
HDG_RMAVAL_PCK.HDG_INSERT_ITEM_TYPE_PRC (
V_THIS_ITEM_TYPE,
V_SPLIT_HDR_ID);
UPDATE HDG_RET_ADJ_LINES_TBL
SET ADJUSTMENT_HEADER_ID = V_SPLIT_HDR_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = V_USER_ID
WHERE CURRENT OF SELECT_LINE;
CMG_UTILITY_PCK.WRITE_OUTPUT('NEW/SPLIT HEADER CREATED FOR THIS CUSTOMER');
CMG_UTILITY_PCK.WRITE_OUTPUT('NEW ADJUSTMENT HEADER ID : ' || TO_CHAR(V_SPLIT_HDR_ID));
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'NEW/SPLIT HEADER CREATED FOR THIS CUSTOMER',
-- 3);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'NEW ADJUSTMENT HEADER ID : ' ||
-- TO_CHAR(V_SPLIT_HDR_ID),
-- 4);
END IF;
*/
END IF;
ELSE
/*
THIS ITEM TYPE WAS ALREADY FOUND IN THE LIST, BUT IT COULD NOW
BELONG TO AN ADJUSTMENT HEADER ID DIFFERENT FROM THE ONE IT IS
CURRENTLY ATTACHED TO. SET IT TO THE APPROPRIATE VALUE
*/
NULL;
/* IF ADJLIN_REC.ADJUSTMENT_HEADER_ID <> V_ADJ_HDR_ID AND
V_THIS_ITEM_TYPE != 'INVALID' THEN
UPDATE HDG_RET_ADJ_LINES_TBL
SET ADJUSTMENT_HEADER_ID = V_ADJ_HDR_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = V_USER_ID
WHERE CURRENT OF SELECT_LINE;
END IF;
*/
END IF;
END LOOP;
/*
PRINT THE LIST OF ITEM TYPES FOUND ON THE REPORT
*/
CMG_UTILITY_PCK.WRITE_OUTPUT ('ITEM TYPES FOUND ARE -');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'ITEM TYPES FOUND ARE -',
-- 2);
FOR ITMTYP_CTR IN 1 .. HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL.COUNT
LOOP
CMG_UTILITY_PCK.WRITE_OUTPUT (
HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL (ITMTYP_CTR).ITEM_TYPE);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL(ITMTYP_CTR).ITEM_TYPE
-- );
IF ITMTYP_CTR > 1
THEN
FOR LINE_REC
IN LINE_CUR (
HDR_REC.ADJUSTMENT_HEADER_ID,
HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL (ITMTYP_CTR).ITEM_TYPE)
LOOP
UPDATE HDG_RET_ADJ_LINES_TBL
SET ADJUSTMENT_HEADER_ID =
HDG_RMAVAL_PCK.DIST_ITEMTYPE_TBL (ITMTYP_CTR).ADJ_HDR_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE ADJUSTMENT_LINE_ID = LINE_REC.ADJUSTMENT_LINE_ID;
END LOOP;
END IF;
END LOOP;
IF V_ONE_OR_MORE_BAD_ITEMS
THEN
-- ROLLBACK TO PRE_BAD_ITMTYPES;
CMG_UTILITY_PCK.WRITE_OUTPUT (
'ONE OR MORE INVALID/BAD ITEMS WERE FOUND');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'ONE OR MORE INVALID/BAD ITEMS WERE FOUND',
-- 2);
END IF;
/*
FOR EACH SUCH CUSTOMER VALIDATED, MAKE SURE THAT INCORRECT HEADER
DETAIL MISMATCH ERRORS DO NOT OCCUR ON ACCOUNT OF HEADERS BEING
SPLIT, THEREBY CAUSING AN IMBALANCE IN HEADER TOTAL_COPIES VS SUM OF
QUANTITIES FOR EACH HEADER
*/
FOR NEWHDR_REC
IN NEW_HDRS_4TALLY_CUR (HDR_REC.TALLY_NR,
HDR_REC.ADJUSTMENT_HEADER_ID)
LOOP
SELECT SUM (NVL (QUANTITY, 0))
INTO V_APPORTIONED_COPIES
FROM HDG.HDG_RET_ADJ_LINES_TBL@prodapps.world
WHERE ADJUSTMENT_HEADER_ID = NEWHDR_REC.ADJUSTMENT_HEADER_ID;
UPDATE HDG_RET_ADJ_HEADER_TBL
SET TOTAL_COPIES = NVL (V_APPORTIONED_COPIES, 0),
LAST_UPDATED_BY = V_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE ADJUSTMENT_HEADER_ID = NEWHDR_REC.ADJUSTMENT_HEADER_ID;
UPDATE HDG_RET_ADJ_HEADER_TBL
SET TOTAL_COPIES =
NVL (TOTAL_COPIES, 0) - NVL (V_APPORTIONED_COPIES, 0),
LAST_UPDATED_BY = V_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE ADJUSTMENT_HEADER_ID = HDR_REC.ADJUSTMENT_HEADER_ID;
END LOOP;
END LOOP;
CMG_UTILITY_PCK.WRITE_OUTPUT ('END OF VALIDATION OF INVALID CUSTOMERS');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'END OF VALIDATION OF INVALID CUSTOMERS',
-- 1);
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- '**************************************',
-- 1);
COMMIT;
END HDG_VALIDATE_CUSTOMERS_PRC;
PROCEDURE HDG_AFFIX_LIKELY_SHIP_TOS_PRC
IS
CURSOR INVALID_SHIP_TOS_CUR
IS
SELECT ADJUSTMENT_HEADER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CUSTOMER_REF_NR,
TALLY_NR,
ADJUSTMENT_FLAG,
RECORD_INTERFACED,
ACCOUNT_NR,
IPDA_MAILBOX_NR,
SOURCE,
TOTAL_COPIES,
CUSTOMER_ID,
CUSTOMER_NAME,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
RETURN_DATE,
PROCESSED_DATE,
EMPLOYEE_ASSIGNED_TO,
ORDER_CATEGORY,
ORDER_TYPE_ID,
VALID_STATUS_FLAG,
DUPLICATE_FLAG,
STORE_NR,
PRICE_LIST_ID,
ORIGINAL_ORDER_HEADER_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL
WHERE NVL (VALID_STATUS_FLAG, 'N') = 'N'
AND NVL (RECORD_INTERFACED, 'N') = 'N'
AND CUSTOMER_ID IS NOT NULL
AND SHIP_TO_SITE_USE_ID IS NULL
FOR UPDATE OF SHIP_TO_SITE_USE_ID NOWAIT;
CURSOR SO_HITS_FOR_SHIP_TO_CUR (
P_CUSTOMER_ID IN NUMBER)
IS -- MODIFIED TO CHECK FOR ACTIVE SHIP TO'S AS PER SCR 7015
SELECT SITE_USE_ID SHIP_TO_SITE_USE_ID
FROM --OE_ORDER_HEADERS_ALL H ,
APPS.RA_CUSTOMERS R, APPS.RA_ADDRESSES A, APPS.RA_SITE_USES S
WHERE R.CUSTOMER_ID = P_CUSTOMER_ID
-- AND H.SOLD_TO_ORG_ID =R.CUSTOMER_ID
AND R.CUSTOMER_ID = A.CUSTOMER_ID
AND A.ADDRESS_ID = S.ADDRESS_ID
AND S.SITE_USE_CODE = 'SHIP_TO'
AND S.STATUS = 'A'
AND ROWNUM = 1;
CURSOR CUSTOMER_RET_LEVEL_CUR (P_CUSTOMER_ID IN NUMBER)
IS
SELECT ATTRIBUTE11 RETURN_LEVEL
FROM APPS.AR_CUSTOMER_PROFILES
WHERE CUSTOMER_ID = P_CUSTOMER_ID;
V_RETURN_LEVEL VARCHAR2 (30);
V_LIKELY_SHIP_TO NUMBER (15);
BEGIN
CMG_UTILITY_PCK.WRITE_OUTPUT ('CLEANING UP INVALID SHIP-TOS');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CLEANING UP INVALID SHIP-TOS');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- '****************************');
FOR SHIPTO_REC IN INVALID_SHIP_TOS_CUR
LOOP
CMG_UTILITY_PCK.WRITE_OUTPUT (
'CUSTOMER ID : ' || TO_CHAR (SHIPTO_REC.CUSTOMER_ID));
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'CUSTOMER ID : ' ||
-- TO_CHAR(SHIPTO_REC.CUSTOMER_ID),
-- 2);
OPEN CUSTOMER_RET_LEVEL_CUR (SHIPTO_REC.CUSTOMER_ID);
FETCH CUSTOMER_RET_LEVEL_CUR INTO V_RETURN_LEVEL;
CLOSE CUSTOMER_RET_LEVEL_CUR;
/*
IF THE CUSTOMER'S RETURN LEVEL IS BILL TO, WE CAN ASSIGN
ANY SHIP-TO THAT HAS SALES ORDERS TO THE CURRENT CUSTOMER.
IF RETURN LEVEL IS SHIP TO OR SHIP/BILL TO HOWEVER, THE
SHIP-TO/STORE# TO RETURN AGAINST MUST BE ON THE RETURN
HEADER. IF THIS DOES NOT EXIST, FORCE ASSOCIATE TO PICK IT
*/
V_LIKELY_SHIP_TO := NULL;
IF V_RETURN_LEVEL = 'BILL TO'
THEN
OPEN SO_HITS_FOR_SHIP_TO_CUR (SHIPTO_REC.CUSTOMER_ID);
FETCH SO_HITS_FOR_SHIP_TO_CUR INTO V_LIKELY_SHIP_TO;
CLOSE SO_HITS_FOR_SHIP_TO_CUR;
END IF;
IF V_LIKELY_SHIP_TO IS NOT NULL
THEN
CMG_UTILITY_PCK.WRITE_OUTPUT (
'MOST LIKELY SHIP-TO DETERMINED TO BE : '
|| TO_CHAR (V_LIKELY_SHIP_TO));
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'MOST LIKELY SHIP-TO DETERMINED TO BE : ' ||
-- TO_CHAR(V_LIKELY_SHIP_TO),
-- 2);
UPDATE HDG_RET_ADJ_HEADER_TBL
SET SHIP_TO_SITE_USE_ID = V_LIKELY_SHIP_TO,
LAST_UPDATED_BY = V_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE CURRENT OF INVALID_SHIP_TOS_CUR;
END IF;
END LOOP;
COMMIT;
CMG_UTILITY_PCK.WRITE_OUTPUT ('END OF SHIP-TO CLEANUP');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- 'END OF SHIP-TO CLEANUP');
--HDG_RMAVAL_PCK.HDG_WRITE_OUT_FILE_PRC (
-- V_FILE_HDL,
-- '**********************');
END HDG_AFFIX_LIKELY_SHIP_TOS_PRC;
PROCEDURE HDG_SHARED_CUST_WAREHOUSE_PRC
IS
/*
THE PURPOSE OF THIS PROCEDURE IS TO TRY TO AFFIX AN
APPROPRIATE WAREHOUSE ID TO THOSE LINES THAT HAVE BEEN
ASSIGNED (TEMPORARILY) TO THE MASTER WAREHOUSE BY THE
INFORMATICA LOAD ROUTINE. FOR ALL SHARED CUSTOMERS
(BUSINESS UNIT = 'MO' AND SET TO FOCUS = 'Y') THE LOAD
ROUTINE WOULD NOT HAVE BEEN ABLE TO ACCURATELY DEFINE
IF THE PRODUCT HAD ORIGINALLY GONE OUT FROM THE HDG/EN
WAREHOUSE. IN THESE CASES, IT UPDATES THE LINES WITH
THE MASTER WAREHOUSE. DURING THIS CLEAN-UP, ALL SUCH
LINES WILL BE PROCESSED AND A SEARCH FOR MATCHING SOS
IN THE HDG WAREHOUSE WILL BE MADE. IF SOS ARE FOUND
FOR THIS, THE RETURN LINE WILL BE MARKED FOR RETURN
AGAINST THE HDG WAREHOUSE. OTHERWISE THEY WILL BE
MARKED FOR PROCESSING AGAINST THE EN WAREHOUSE.
NOTE: A MAJOR ASSUMPTION HERE IS THAT THE SAME PRODUCT
WILL NOT BE SENT TO THE SAME CUSTOMER FROM BOTH
THE WAREHOUSES.
*/
CURSOR MASTER_WAREHOUSE_LINES_CUR
IS
SELECT /*+ RULE */
L.INVENTORY_ITEM_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L, HDG.HDG_RET_ADJ_HEADER_TBL H --,
--HR_ORGANIZATION_UNITS ORG
WHERE H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.SHIP_TO_SITE_USE_ID IS NOT NULL
AND H.CUSTOMER_ID IS NOT NULL
AND L.INVENTORY_ITEM_ID IS NOT NULL
AND L.WAREHOUSE_ID =
(SELECT ORGANIZATION_ID
FROM HR_ORGANIZATION_UNITS
WHERE NAME = 'HEARST MASTER ORGANIZATION')
--AND L.WAREHOUSE_ID = ORG.ORGANIZATION_ID
--AND ORG.NAME = 'HEARST MASTER ORGANIZATION'
AND H.RECORD_INTERFACED != 'Y'
FOR UPDATE OF L.WAREHOUSE_ID NOWAIT;
CURSOR LIST_OF_VALID_ORGS_CUR (
P_ORG_NAME IN VARCHAR2,
P_INV_ITEM_ID IN NUMBER)
IS
SELECT ITM.ORGANIZATION_ID
FROM HR_ORGANIZATION_UNITS ORG, MTL_SYSTEM_ITEMS ITM
WHERE ORG.NAME = P_ORG_NAME
AND ORG.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = P_INV_ITEM_ID;
V_VALID_ORG_ID NUMBER;
BEGIN
/*
FIRST OPEN ALL THE LINES THAT ARE CURRENTLY ASSIGNED
TO THE MASTER ORG. FOR EACH SUCH LINE SELECTED, OPEN
THE LIST OF VALID ORGANIZATIONS TO WHICH THE LINE CAN
BE ASSIGNED. FOR EACH SUCH ASSIGNABLE ORGANIZATION,
SEARCH FOR THE ITEM IN THE HDG WAREHOUSE. IF THE ITEM
EXISTS IN HDG IT IS ASSUMED THAT IT MUST HAVE BEEN
SOLD FROM HDG. IF IT DOES NOT EXIST IN HDG, ASSIGN
IT TO EN.
*/
FOR LINE_REC IN MASTER_WAREHOUSE_LINES_CUR
LOOP
/*
FIRST LOOK FOR THE ITEM IN THE HDG WAREHOUSE
*/
OPEN LIST_OF_VALID_ORGS_CUR ('HEARST DISTRIBUTION GROUP ORGANIZATION',
LINE_REC.INVENTORY_ITEM_ID);
FETCH LIST_OF_VALID_ORGS_CUR INTO V_VALID_ORG_ID;
IF LIST_OF_VALID_ORGS_CUR%NOTFOUND
THEN
/*
LOOK FOR IT IN THE EN WAREHOUSE, SINCE IT IS NOT IN HDG
*/
CLOSE LIST_OF_VALID_ORGS_CUR;
OPEN LIST_OF_VALID_ORGS_CUR ('EASTERN NEWS ORGANIZATION',
LINE_REC.INVENTORY_ITEM_ID);
FETCH LIST_OF_VALID_ORGS_CUR INTO V_VALID_ORG_ID;
END IF;
CLOSE LIST_OF_VALID_ORGS_CUR;
UPDATE HDG_RET_ADJ_LINES_TBL
SET WAREHOUSE_ID = V_VALID_ORG_ID,
INVALID_REASON_CODE = NULL,
VALID_STATUS_FLAG = NULL,
LAST_UPDATED_BY = V_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE CURRENT OF MASTER_WAREHOUSE_LINES_CUR;
END LOOP;
COMMIT;
END HDG_SHARED_CUST_WAREHOUSE_PRC;
PROCEDURE HDG_FIX_MISSING_CUST_REF_PRC
IS
CURSOR CUST_REF_CUR
IS
SELECT ADJUSTMENT_HEADER_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL
WHERE RTRIM (CUSTOMER_REF_NR) IS NULL
AND ADJUSTMENT_FLAG != 'Y'
AND RECORD_INTERFACED = 'N';
BEGIN
FOR CUST_REF_REC IN CUST_REF_CUR
LOOP
UPDATE HDG_RET_ADJ_HEADER_TBL
SET CUSTOMER_REF_NR =
'ROAR#'
|| LPAD (TO_CHAR (HDG_MISSING_CUST_REF_SEQ.NEXTVAL),
5,
'0'),
LAST_UPDATED_BY = V_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE ADJUSTMENT_HEADER_ID = CUST_REF_REC.ADJUSTMENT_HEADER_ID;
END LOOP;
COMMIT;
END HDG_FIX_MISSING_CUST_REF_PRC;
PROCEDURE HDG_FIX_BAD_PRODUCTS_PRC
IS
CURSOR ADJ_LIN_CUR
IS
SELECT /*+ RULE */
L.ADJUSTMENT_LINE_ID, L.ISBN, H.SOURCE
FROM HDG.HDG_RET_ADJ_LINES_TBL L, HDG.HDG_RET_ADJ_HEADER_TBL H
WHERE L.ADJUSTMENT_HEADER_ID = H.ADJUSTMENT_HEADER_ID
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND INVALID_REASON_CODE = 'BAD PRODUCT'
AND H.RECORD_INTERFACED = 'N';
CURSOR ITEM_CUR (
P_ISBN IN VARCHAR2,
P_ORG_NAME IN VARCHAR2)
IS
SELECT INVENTORY_ITEM_ID, ITEM.ORGANIZATION_ID, SEGMENT1
FROM MTL_SYSTEM_ITEMS ITEM, APPS.HR_ORGANIZATION_UNITS ORG
WHERE ITEM.ATTRIBUTE6 = P_ISBN
AND ITEM.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.NAME = P_ORG_NAME;
V_INVENTORY_ITEM_ID NUMBER;
V_WAREHOUSE_ID NUMBER;
V_ITEM_NUMBER VARCHAR2 (25);
V_ORG_NAME VARCHAR2 (50);
BEGIN
FOR ADJ_LIN_REC IN ADJ_LIN_CUR
LOOP
IF ADJ_LIN_REC.SOURCE IN ('MO', 'WK', 'BK')
THEN
V_ORG_NAME := 'HEARST DISTRIBUTION GROUP ORGANIZATION';
ELSIF ADJ_LIN_REC.SOURCE = 'EN'
THEN
V_ORG_NAME := 'EASTERN NEWS ORGANIZATION';
ELSE
V_ORG_NAME := 'HEARST DISTRIBUTION GROUP ORGANIZATION';
END IF;
OPEN ITEM_CUR (ADJ_LIN_REC.ISBN, V_ORG_NAME);
FETCH ITEM_CUR
INTO V_INVENTORY_ITEM_ID, V_WAREHOUSE_ID, V_ITEM_NUMBER;
IF ITEM_CUR%FOUND
THEN
UPDATE HDG_RET_ADJ_LINES_TBL
SET INVALID_REASON_CODE = 'BAD ISSUE',
INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID,
WAREHOUSE_ID = V_WAREHOUSE_ID,
ITEM_NR = V_ITEM_NUMBER
WHERE ADJUSTMENT_LINE_ID = ADJ_LIN_REC.ADJUSTMENT_LINE_ID;
END IF;
CLOSE ITEM_CUR;
END LOOP;
COMMIT;
END;
PROCEDURE HDG_FIX_ADD_ON_CODES_PRC
IS
CURSOR HDG_RET_ADJ_LINES
IS
SELECT /*+ RULE */
INVENTORY_ITEM_ID,
WAREHOUSE_ID,
ISSUE_CODE,
ADJUSTMENT_LINE_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L, HDG.HDG_RET_ADJ_HEADER_TBL H
WHERE INVALID_REASON_CODE = 'BAD ISSUE'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND NVL (L.USER_REJECTED_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
FOR UPDATE OF ADJUSTMENT_LINE_ID;
CURSOR HDG_ISSUE (
P_ITEM_ID IN NUMBER,
P_WAREHOUSE_ID IN NUMBER,
P_ISSUE_CODE IN VARCHAR2)
IS
SELECT ATTRIBUTE7, ATTRIBUTE2
FROM MTL_ITEM_REVISIONS
WHERE ORGANIZATION_ID = P_WAREHOUSE_ID
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND SUBSTR (ATTRIBUTE2, -2) = SUBSTR (P_ISSUE_CODE, -2)
AND SUBSTR (ATTRIBUTE7, -2) != SUBSTR (P_ISSUE_CODE, -2)
ORDER BY ATTRIBUTE7 DESC;
BEGIN
FOR HDG_LINES IN HDG_RET_ADJ_LINES
LOOP
FOR HDG_ISSUE_REC
IN HDG_ISSUE (HDG_LINES.INVENTORY_ITEM_ID,
HDG_LINES.WAREHOUSE_ID,
HDG_LINES.ISSUE_CODE)
LOOP
IF HDG_LINES.ISSUE_CODE != SUBSTR (HDG_ISSUE_REC.ATTRIBUTE7, -4)
THEN
UPDATE HDG_RET_ADJ_LINES_TBL
SET ISSUE_CODE = HDG_ISSUE_REC.ATTRIBUTE7,
VALID_STATUS_FLAG = NULL,
INVALID_REASON_CODE = NULL
WHERE CURRENT OF HDG_RET_ADJ_LINES;
END IF;
EXIT;
END LOOP;
END LOOP;
COMMIT;
END HDG_FIX_ADD_ON_CODES_PRC;
PROCEDURE HDG_IPD_BIPAD_FIX_PRC
IS
CURSOR HDR_CUR
IS
SELECT ADJUSTMENT_LINE_ID,
DECODE (L.ISBN,
'99150', '10150',
'99151', '10151',
'99778', '18778',
'98782', '18782',
'99792', '47792',
'99795', '47795',
'99796', '47796',
'99798', '47798',
'97127', '47799',
'99427', '48427',
'99674', '48674',
'99977', '89077',
'99152', '01152',
'99983', '01683',
'99986', '01986',
'92143', '02143',
'97130', '02173',
'99349', '02340',
'97129', '02341',
'99396', '02396',
'99473', '02743',
'99757', '02757',
'99969', '02969',
'99992', '02992',
'99994', '02994',
'99995', '02995',
'93207', '03207',
'93423', '03423',
'99577', '03577',
'99604', '03604',
'99653', '03653',
'93902', '03902',
'93904', '03904',
'93905', '03905',
'93906', '03906',
'93907', '03907',
'93909', '03909',
'93914', '03914',
'93915', '03915',
'93919', '03919',
'93920', '03920',
'93925', '03925',
'93927', '03927',
'93929', '03929',
'99490', '08490',
'99497', '08497',
L.ISBN)
BIPAD
FROM APPS.HDG_RET_ADJ_LINES_TBL L, APPS.HDG_RET_ADJ_HEADER_TBL H
WHERE L.ISBN IN
('93902',
'99427',
'99674',
'99977',
'99152',
'99983',
'99986',
'92143',
'97130',
'99349',
'97129',
'99396',
'99473',
'99757',
'99969',
'99992',
'99994',
'99995',
'93207',
'93423',
'99577',
'99604',
'99653',
'93904',
'93905',
'93906',
'93907',
'93909',
'93914',
'93915',
'93919',
'93920',
'93925',
'93927',
'93929',
'99490',
'99497')
AND NVL (L.LINE_INTERFACED, 'N') = 'N'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N'
AND H.CUSTOMER_ID = 3257; /* IPD SOLANA BEACH CUSTOMER */
CURSOR ITEM_CUR (
P_BIPAD IN VARCHAR2)
IS
SELECT INVENTORY_ITEM_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ATTRIBUTE6 = P_BIPAD
AND ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG');
V_INVENTORY_ITEM_ID NUMBER;
BEGIN
FOR HDR_REC IN HDR_CUR
LOOP
OPEN ITEM_CUR (HDR_REC.BIPAD);
FETCH ITEM_CUR INTO V_INVENTORY_ITEM_ID;
IF ITEM_CUR%FOUND
THEN
UPDATE APPS.HDG_RET_ADJ_LINES_TBL
SET BIPAD = HDR_REC.BIPAD,
ISBN = HDR_REC.BIPAD,
INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
END IF;
CLOSE ITEM_CUR;
END LOOP;
COMMIT;
END HDG_IPD_BIPAD_FIX_PRC;
PROCEDURE HDG_REJECT_COSMO_P_MECH_PRC
IS
CURSOR LINE_CUR
IS
SELECT /*+ RULE */
DISTINCT L.ADJUSTMENT_LINE_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L, HDG.HDG_RET_ADJ_HEADER_TBL H
WHERE L.ISBN IN ('08638', '08233', '08140', '08443')
AND L.INVENTORY_ITEM_ID IN (1400, 1598, 1491, 53849)
AND L.ISSUE_CODE >
DECODE (L.ISBN,
'08638', 200202,
'08233', 200203,
'08140', 200202,
'08443', 200202)
AND NVL (LINE_INTERFACED, 'N') != 'Y'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N'
AND H.CUSTOMER_ID = 3367 -- 'M-RETAIL-VISION-MIDDLEBURY-CSR'
AND H.ORDER_CATEGORY = 'RMA';
BEGIN
/* THE FOLLOWING PROCEDURE IS ADDED TO
REJECT THE RETURNS AS 'NON-RETURNABLE'FOR THE
FOLLOWING TITLES AND ISSUES FOR THE CUSTOMER
'M-RETAIL VISION-MIDDLEBURY-CSR'
TITLE BIPAD ISSUE
POPULAR MECHANICS 08638 200203 ISSUE ONWARDS
COSMOPOLITAN 08233 200204 ISSUE ONWARDS
BIOGRAPHY 08140 200203 ISSUE ONWARDS
VANIT FAIR 08443 200203 ISSUE ONWARDS
*/
FOR LINE_REC IN LINE_CUR
LOOP
UPDATE HDG_RET_ADJ_LINES_TBL
SET LINE_INTERFACED = 'N',
USER_REJECTED_FLAG = 'Y',
UNIT_PRICE = 0,
INVALID_REASON_CODE = 'NON-RETURNABLE'
WHERE ADJUSTMENT_LINE_ID = LINE_REC.ADJUSTMENT_LINE_ID;
END LOOP;
END HDG_REJECT_COSMO_P_MECH_PRC;
PROCEDURE HDG_BIPAD_FIX_PRC
IS
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
ADJUSTMENT_LINE_ID,
DECODE (L.ISBN,
'64798', '56526',
'64807', '56528',
'64797', '56525',
'64851', '56527',
'64904', '56529',
'64715', '08557',
'39026', '08556',
L.ISBN)
BIPAD
FROM APPS.HDG_RET_ADJ_LINES_TBL L, APPS.HDG_RET_ADJ_HEADER_TBL H
WHERE L.ISBN IN
('64798',
'64807',
'64797',
'64851',
'56527',
'64715',
'39026')
AND NVL (L.LINE_INTERFACED, 'N') = 'N'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N';
CURSOR ITEM_CUR (
P_BIPAD IN VARCHAR2)
IS
SELECT INVENTORY_ITEM_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ATTRIBUTE6 = P_BIPAD
AND ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG');
V_INVENTORY_ITEM_ID NUMBER;
BEGIN
/* FINE HOMEBUILDING BIPAD HAS BEEN CHANGED TO 56526 IN THE ITEMSETUP
FINE GARDENING 64807 TO 56528
FINE WOODWORKING 64797 TO 56525
THREADS 64851 TO 56527 -- CORAL EMAIL 07/18/02
FINE COOKING 64904 TO 56529 -- CORAL EMAIL 07/18/02
SAILING WORLD 64715 TO 08557 -- JUDY EMAIL 08/28/02
CRUISING WORLD 39026 TO 08556 -- JUDY EMAIL 08/28/02
*/
FOR HDR_REC IN HDR_CUR
LOOP
OPEN ITEM_CUR (HDR_REC.BIPAD);
FETCH ITEM_CUR INTO V_INVENTORY_ITEM_ID;
IF ITEM_CUR%FOUND
THEN
UPDATE APPS.HDG_RET_ADJ_LINES_TBL
SET BIPAD = HDR_REC.BIPAD,
ISBN = HDR_REC.BIPAD,
INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
END IF;
CLOSE ITEM_CUR;
END LOOP;
COMMIT;
END HDG_BIPAD_FIX_PRC;
PROCEDURE HDG_REJ_TVG_CUSTOMER_PRC
IS
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
ADJUSTMENT_LINE_ID
FROM APPS.HDG_RET_ADJ_LINES_TBL L,
APPS.HDG_RET_ADJ_HEADER_TBL H,
APPS.MTL_SYSTEM_ITEMS MSI
WHERE L.ISBN = MSI.ATTRIBUTE6
AND MSI.DESCRIPTION LIKE 'TV GUIDE%'
AND MSI.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND MSI.ATTRIBUTE3 LIKE '18%'
AND MSI.SEGMENT1 LIKE '018%'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.CUSTOMER_ID IN (3443, 3444, 3445)
AND H.ORDER_CATEGORY = 'RMA';
BEGIN
FOR HDR_REC IN HDR_CUR
LOOP
UPDATE HDG_RET_ADJ_LINES_TBL
SET USER_REJECTED_FLAG = 'Y',
UNIT_PRICE = 0,
INVALID_REASON_CODE = 'NON-RETURNABLE'
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
END LOOP;
END HDG_REJ_TVG_CUSTOMER_PRC;
PROCEDURE HDG_RET_BIPAD_MAPPING_PRC
IS
/*
As part of SCR 7015 We have added a new bipad mapping table in order to perform all the mappings without User intervention.
In this procedure we are trying to check all the return's that are not interfaced against the bipad mapping table and check if the issue code of the
the newly mapped bipad is in the MTL revisions if exists then process updates the new bipad info in the return table else
the process updates the new bipad in the return table but the invalid reason code is set to Bad Issue.
*/
-- This Cursor is to get all the lines from the return table whose bipads are setup in the Bipad Mapping table
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
ADJUSTMENT_LINE_ID,
B.NEW_BIPAD,
B.OLD_BIPAD,
L.ISSUE_CODE
FROM APPS.HDG_RET_ADJ_LINES_TBL L,
APPS.HDG_RET_ADJ_HEADER_TBL H,
HDG.CMG_RET_BIPAD_MAP_TBL B
WHERE L.ISBN = B.OLD_BIPAD
AND NVL (L.LINE_INTERFACED, 'N') = 'N'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N';
-- This cursor is to check if the new bipad and its corresponding issue code onsale date is falling between the start onsales and end onsale
-- of the bipad mapping
CURSOR ITEM_CUR (
P_NEW_BIPAD IN VARCHAR2,
P_OLD_BIPAD IN VARCHAR2,
P_ISSUE IN VARCHAR2)
IS
SELECT R.INVENTORY_ITEM_ID,
R.EFFECTIVITY_DATE,
S.ORGANIZATION_ID,
S.SEGMENT1,
R.ATTRIBUTE10
FROM APPS.MTL_SYSTEM_ITEMS S,
APPS.MTL_ITEM_REVISIONS R,
HDG.CMG_RET_BIPAD_MAP_TBL M
WHERE S.ATTRIBUTE6 = P_NEW_BIPAD
AND R.ATTRIBUTE7 = P_ISSUE
AND S.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND R.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND M.OLD_BIPAD = P_OLD_BIPAD
AND M.NEW_BIPAD = P_NEW_BIPAD
AND M.NEW_BIPAD = S.ATTRIBUTE6
AND M.ENABLE_FLAG = 'Y'
AND R.EFFECTIVITY_DATE >= START_ONSALE_DATE
AND R.EFFECTIVITY_DATE <=
NVL (END_ONSALE_DATE, '31-DEC-2099');
-- This cursor is to check for the issue codes not falling under the mapping start and end dates. If issue codes doesn't exist
-- then update the new bipad in the return table with invalid reason code as 'BAD ISSUE'.
CURSOR ITEM_ISS_CUR (
P_NEW_BIPAD IN VARCHAR2,
P_ISSUE IN VARCHAR2)
IS
SELECT S.INVENTORY_ITEM_ID,
S.ORGANIZATION_ID,
S.SEGMENT1,
R.ATTRIBUTE10
FROM APPS.MTL_SYSTEM_ITEMS S, APPS.MTL_ITEM_REVISIONS R
WHERE S.ATTRIBUTE6 = P_NEW_BIPAD
AND R.ATTRIBUTE7 = P_ISSUE
AND S.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND R.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID;
V_INVENTORY_ITEM_ID NUMBER;
V_INVENTORY_ITEM_ID_1 NUMBER;
V_ONSALE_DATE DATE;
V_ORG_ID NUMBER;
V_ORG_ID1 NUMBER;
V_ITEM_NR VARCHAR2 (10);
V_ITEM_NR1 VARCHAR2 (10);
V_UPC NUMBER;
V_UPC1 NUMBER;
--TEST1 VARCHAR2(20);
--TEST2 VARCHAR2(20);
--TEST3 VARCHAR2(20);
--TEST4 VARCHAR2(20);
--TEST5 VARCHAR2(20);
--TEST6 NUMBER;
BEGIN
FOR HDR_REC IN HDR_CUR
LOOP
OPEN ITEM_CUR (HDR_REC.NEW_BIPAD,
HDR_REC.OLD_BIPAD,
HDR_REC.ISSUE_CODE);
FETCH ITEM_CUR
INTO V_INVENTORY_ITEM_ID, V_ONSALE_DATE, V_ORG_ID, V_ITEM_NR, V_UPC;
--CMG_UTILITY_PCK.WRITE_OUTPUT('BIPAD MAP TEST1');
IF ITEM_CUR%FOUND
THEN
UPDATE HDG.HDG_RET_ADJ_LINES_TBL
SET BIPAD = HDR_REC.NEW_BIPAD,
ISBN = HDR_REC.NEW_BIPAD,
INVALID_REASON_CODE = NULL,
VALID_STATUS_FLAG = NULL,
INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID,
WAREHOUSE_ID = V_ORG_ID,
ITEM_NR = V_ITEM_NR,
UPC = V_UPC
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
ELSE
-- CMG_UTILITY_PCK.WRITE_OUTPUT('BIPAD MAP TEST2');
OPEN ITEM_ISS_CUR (HDR_REC.NEW_BIPAD, HDR_REC.ISSUE_CODE);
FETCH ITEM_ISS_CUR
INTO V_INVENTORY_ITEM_ID_1, V_ORG_ID1, V_ITEM_NR1, V_UPC1;
IF ITEM_ISS_CUR%NOTFOUND
THEN
UPDATE HDG.HDG_RET_ADJ_LINES_TBL
SET BIPAD = HDR_REC.NEW_BIPAD,
ISBN = HDR_REC.NEW_BIPAD,
INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID_1,
INVALID_REASON_CODE = 'BAD ISSUE',
WAREHOUSE_ID = V_ORG_ID1,
ITEM_NR = V_ITEM_NR,
UPC = V_UPC1
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
END IF;
CLOSE ITEM_ISS_CUR;
END IF;
CLOSE ITEM_CUR;
END LOOP;
COMMIT;
END HDG_RET_BIPAD_MAPPING_PRC;
PROCEDURE HDG_RET_ISSUE_MAPPING_PRC
IS
/*
As part of SCR 7015 We have added a new issue mapping table in order to perform all the mappings without User intervention.
In this procedure we are trying to check all the return's that are not interfaced against the issue mapping table and check if the new issue code of the
the bipad is in the MTL revisions if exists then process updates the new issue info in the return table
*/
-- This cursor is to check if the bipad and its corresponding new issue code return date/sysdate is falling between the start date and end date
-- of the issue mapping
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
ADJUSTMENT_LINE_ID,
B.BIPAD,
B.OLD_ISSUE,
B.NEW_ISSUE,
L.ISSUE_CODE
FROM APPS.HDG_RET_ADJ_LINES_TBL L,
APPS.HDG_RET_ADJ_HEADER_TBL H,
HDG.CMG_RET_ISSUE_MAP_TBL B
WHERE L.ISBN = B.BIPAD
AND L.ISSUE_CODE = B.OLD_ISSUE
AND B.ENABLE_FLAG = 'Y'
AND SYSDATE BETWEEN B.START_DATE
AND NVL (B.END_DATE, '31-DEC-2099')
AND NVL (L.LINE_INTERFACED, 'N') = 'N'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N';
-- This cursor is to check if the bipad and the mapped new issue codes are setup in the mtl revisions.
CURSOR ITEM_CUR (
P_BIPAD IN VARCHAR2,
P_ISSUE IN VARCHAR2)
IS
SELECT R.INVENTORY_ITEM_ID, R.ATTRIBUTE7, R.ATTRIBUTE10
FROM APPS.MTL_SYSTEM_ITEMS S, APPS.MTL_ITEM_REVISIONS R
WHERE S.ATTRIBUTE6 = P_BIPAD
AND R.ATTRIBUTE7 = P_ISSUE
AND S.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND R.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID;
V_BIPAD VARCHAR2 (5);
V_ISSUE VARCHAR2 (6);
V_UPC VARCHAR2 (10);
BEGIN
-- CMG_UTILITY_PCK.WRITE_OUTPUT('ISSUE MAP TEST1');
FOR HDR_REC IN HDR_CUR
LOOP
OPEN ITEM_CUR (HDR_REC.BIPAD, HDR_REC.NEW_ISSUE);
FETCH ITEM_CUR
INTO V_BIPAD, V_ISSUE, V_UPC;
-- CMG_UTILITY_PCK.WRITE_OUTPUT('ISSUE MAP TEST2');
IF ITEM_CUR%FOUND
THEN
UPDATE HDG.HDG_RET_ADJ_LINES_TBL
SET ISSUE_CODE = V_ISSUE,
VALID_STATUS_FLAG = NULL,
INVALID_REASON_CODE = NULL,
UPC = V_UPC
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID;
END IF;
CLOSE ITEM_CUR;
END LOOP;
END HDG_RET_ISSUE_MAPPING_PRC;
PROCEDURE HDG_RET_ISSUE_ADDON_FIX_PRC
IS
/*
As part of SCR 7015 We have added a new condition to check all the lines which are not interfaced to Order Management.
The process will check if there is any latest issue code for the existing line based on the add on code of the orignal issue code
and also the onsale date of the latest issue code should be less than the return date and the quantity of the line should be greater
than 10 for the process to update the latest issue code by doing this we avoid lot of LATE rejects.
*/
/* this cursor is to pull all the lines which are not interfaced to Order Managerment*/
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
ADJUSTMENT_LINE_ID,
L.BIPAD,
L.ISSUE_CODE,
L.INVALID_REASON_CODE,
L.WAREHOUSE_ID
FROM APPS.HDG_RET_ADJ_LINES_TBL L, APPS.HDG_RET_ADJ_HEADER_TBL H
WHERE NVL (L.LINE_INTERFACED, 'N') = 'N'
AND NVL (L.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND NVL (L.USER_REJECTED_FLAG, 'N') = 'N'
AND H.RECORD_INTERFACED = 'N'
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N';
/* This cursor is to check if the bipad and issue are valid */
CURSOR ITEM_CUR (
P_BIPAD IN VARCHAR2,
P_ISSUE IN VARCHAR2)
IS
SELECT S.ATTRIBUTE6,
R.ATTRIBUTE7,
R.INVENTORY_ITEM_ID,
R.ATTRIBUTE2,
R.EFFECTIVITY_DATE ONSALEDATE,
R.ATTRIBUTE11 OFFSALEDATE,
R.ATTRIBUTE12 LASTRETURNDATE
FROM APPS.MTL_SYSTEM_ITEMS S, APPS.MTL_ITEM_REVISIONS R
WHERE S.ATTRIBUTE6 = P_BIPAD
AND R.ATTRIBUTE7 = P_ISSUE
AND S.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND R.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID;
/* this cursor will pull the latest issue code of the orignal issue code based on the add on code*/
CURSOR ADDON_CHECK_CUR (
P_ITEM_ID IN NUMBER,
P_ISSUE_CODE IN VARCHAR2,
P_WAREHOUSE_ID IN NUMBER)
IS
SELECT DISTINCT ATTRIBUTE7,
ATTRIBUTE2,
INVENTORY_ITEM_ID,
EFFECTIVITY_DATE
FROM MTL_ITEM_REVISIONS
WHERE ORGANIZATION_ID = P_WAREHOUSE_ID
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND SUBSTR (ATTRIBUTE2, -2) = SUBSTR (P_ISSUE_CODE, -2)
--AND SUBSTR(ATTRIBUTE7,-2) != SUBSTR(:P_ISSUE_CODE,-2)
AND ATTRIBUTE7 > P_ISSUE_CODE
AND EFFECTIVITY_DATE < SYSDATE
AND ROWNUM = 1
ORDER BY ATTRIBUTE7 DESC;
BEGIN
FOR HDR_REC IN HDR_CUR
LOOP
FOR ITEM_REC IN ITEM_CUR (HDR_REC.BIPAD, HDR_REC.ISSUE_CODE)
LOOP
FOR ADDON_REC
IN ADDON_CHECK_CUR (ITEM_REC.INVENTORY_ITEM_ID,
ITEM_REC.ATTRIBUTE7,
HDR_REC.WAREHOUSE_ID)
LOOP
-- CMG_UTILITY_PCK.WRITE_OUTPUT('ISSUE ADDON FIX TEST1');
UPDATE HDG.HDG_RET_ADJ_LINES_TBL
SET ISSUE_CODE = ADDON_REC.ATTRIBUTE7,
INVALID_REASON_CODE = NULL,
VALID_STATUS_FLAG = NULL
WHERE ADJUSTMENT_LINE_ID = HDR_REC.ADJUSTMENT_LINE_ID
AND QUANTITY > 10;
END LOOP;
END LOOP;
END LOOP;
END HDG_RET_ISSUE_ADDON_FIX_PRC;
PROCEDURE HDG_FIX_HDR_DTL_MISMATCH_PRC
IS
CURSOR HDR_CUR
IS
SELECT /*+ RULE */
H.ADJUSTMENT_HEADER_ID,
SUM (H.TOTAL_COPIES) HDR_COPIES,
SUM (L.ORIGINAL_RETURN_QUANTITY) LINE_COPIES
FROM HDG_RET_ADJ_LINES_TBL L, HDG_RET_ADJ_HEADER_TBL H
WHERE H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND NVL (H.VALID_STATUS_FLAG, 'N') = 'N'
AND H.RECORD_INTERFACED != 'Y'
AND NVL (H.DUPLICATE_FLAG, 'N') != 'Y'
AND ORDER_CATEGORY = 'RMA'
AND NVL (H.ADJUSTMENT_FLAG, 'N') = 'N'
GROUP BY H.ADJUSTMENT_HEADER_ID;
BEGIN
FOR HDR_REC IN HDR_CUR
LOOP
IF HDR_REC.HDR_COPIES <> HDR_REC.LINE_COPIES
THEN
UPDATE HDG_RET_ADJ_HEADER_TBL
SET TOTAL_COPIES = HDR_REC.LINE_COPIES
WHERE ADJUSTMENT_HEADER_ID = HDR_REC.ADJUSTMENT_HEADER_ID;
END IF;
END LOOP;
END HDG_FIX_HDR_DTL_MISMATCH_PRC;
PROCEDURE GEN_ORDERTYPE_ASSIGN_PRC
IS --Genera Foreign Accounts SCR 8385
/* pEFORM THE SPLIT FOR THE ND'S WHICH HAS SPLIT ORDER TYPES*/
CURSOR WH_PFT_CUR IS
SELECT COUNT(*),WHOLESALER_PROFITCENTER_ID,WHOLESALER_PC_NAME
FROM HDG.GEN_PROFITCENTER_MAPPING_TBL
GROUP BY WHOLESALER_PROFITCENTER_ID,WHOLESALER_PC_NAME
HAVING COUNT(*) > 1;
/* This cursor pulls the list of headers that have mixed profitcenter data for an header */
CURSOR HDR_CUR (P_WH_PC_NAME IN VARCHAR2)
IS
--SELECT ADJUSTMENT_HEADER_ID ,COUNT(*) V_COUNT
--FROM(
SELECT H.ADJUSTMENT_HEADER_ID,
DECODE (GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE),
'NO ISSUE PROFITCENTER', M.PROFITCENTER_ID,
GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE))
ISS_PCT_ID,
M.PROFITCENTER_ID WH_PC_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L,
HDG.HDG_RET_ADJ_HEADER_TBL H,
APPS.RA_CUSTOMERS C,
APPS.CMG_LOGO_MAPPING_VW M
WHERE H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.VALID_STATUS_FLAG = 'Y'
AND H.RECORD_INTERFACED != 'Y'
AND NVL (H.DUPLICATE_FLAG, 'N') != 'Y'
AND L.LINE_INTERFACED != 'Y'
AND L.VALID_STATUS_FLAG = 'Y'
AND H.PCT_SPLIT_FLAG IS NULL
AND H.CUSTOMER_ID = C.CUSTOMER_ID
AND M.CUSTOMER_NAME = SUBSTR (H.CUSTOMER_NAME, 1, 1)
AND M.CUSTOMER_NAME = P_WH_PC_NAME
-- AND H.ADJUSTMENT_HEADER_ID=1528959--1528936
GROUP BY H.ADJUSTMENT_HEADER_ID,
DECODE (GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE),
'NO ISSUE PROFITCENTER', M.PROFITCENTER_ID,
GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE)),
M.PROFITCENTER_ID;
-- )
--GROUP BY ADJUSTMENT_HEADER_ID;
/* This cursor pulls all the information for an header from Returns header table based on the issue profit center grouping */
CURSOR HDR_RET_CUR (
P_HEADER_ID IN NUMBER)
IS
SELECT DISTINCT
T.NAME,
H.ADJUSTMENT_HEADER_ID,
M.PROFITCENTER_ID WH_PCT_ID,
DECODE (GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE),
'NO ISSUE PROFITCENTER', M.PROFITCENTER_ID,
GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE))
ISS_PCT_ID,
H.TALLY_NR,
H.CUSTOMER_REF_NR,
H.ADJUSTMENT_FLAG,
H.CREATED_BY,
SUM (L.QUANTITY) TOT_COPIES,
H.ACCOUNT_NR,
H.IPDA_MAILBOX_NR,
H.SOURCE,
H.CUSTOMER_ID,
H.CUSTOMER_NAME,
H.BILL_TO_SITE_USE_ID,
H.SHIP_TO_SITE_USE_ID,
H.RETURN_DATE,
H.PROCESSED_DATE,
H.EMPLOYEE_ASSIGNED_TO,
H.ORDER_CATEGORY,
H.ORDER_TYPE_ID,
H.PRICE_LIST_ID,
H.LAST_UPDATED_BY,
H.SALESREP_ID,
H.STORE_NR,
H.DUPLICATE_FLAG,
H.VALID_STATUS_FLAG,
H.SALES_CHANNEL_CODE,
H.ORIGINAL_ORDER_HEADER_ID,
H.SOH_ORIGINAL_SYSTEM_REF,
H.ROAR_THREAD_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL H,
HDG.HDG_RET_ADJ_LINES_TBL L,
APPS.RA_CUSTOMERS R,
APPS.CMG_LOGO_MAPPING_VW M,
APPS.OE_TRANSACTION_TYPES T
WHERE H.ADJUSTMENT_HEADER_ID = P_HEADER_ID
AND H.ADJUSTMENT_HEADER_ID = L.ADJUSTMENT_HEADER_ID
AND H.CUSTOMER_ID = R.CUSTOMER_ID
-- AND L.INVALID_REASON_CODE IS NULL
AND M.CUSTOMER_NAME = SUBSTR (H.CUSTOMER_NAME, 1, 1)
AND H.ORDER_TYPE_ID = T.TRANSACTION_TYPE_ID
GROUP BY T.NAME,
M.PROFITCENTER_ID,
H.ADJUSTMENT_HEADER_ID,
--GET_ISSPC_FNC(L.BIPAD, L.ISSUE_CODE),
DECODE (GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE),
'NO ISSUE PROFITCENTER', M.PROFITCENTER_ID,
GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE)),
H.TALLY_NR,
H.CUSTOMER_REF_NR,
H.ADJUSTMENT_FLAG,
H.CREATED_BY,
H.ACCOUNT_NR,
H.IPDA_MAILBOX_NR,
H.SOURCE,
H.CUSTOMER_ID,
H.CUSTOMER_NAME,
H.BILL_TO_SITE_USE_ID,
H.SHIP_TO_SITE_USE_ID,
H.RETURN_DATE,
H.PROCESSED_DATE,
H.EMPLOYEE_ASSIGNED_TO,
H.ORDER_CATEGORY,
H.ORDER_TYPE_ID,
H.PRICE_LIST_ID,
H.LAST_UPDATED_BY,
H.SALESREP_ID,
H.STORE_NR,
H.DUPLICATE_FLAG,
H.VALID_STATUS_FLAG,
H.SALES_CHANNEL_CODE,
H.ORIGINAL_ORDER_HEADER_ID,
H.SOH_ORIGINAL_SYSTEM_REF,
H.ROAR_THREAD_ID;
/* Gets list of all the lines for an header and issue profit center */
CURSOR HDL_RET_CUR (
P_HDR_ID IN NUMBER,
P_ISS_PCT IN NUMBER,
P_WH_PCT IN NUMBER)
IS
SELECT *
FROM HDG.HDG_RET_ADJ_LINES_TBL L
WHERE L.ADJUSTMENT_HEADER_ID = P_HDR_ID
AND DECODE (GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE),
'NO ISSUE PROFITCENTER', P_WH_PCT,
GET_ISSPC_FNC (L.BIPAD, L.ISSUE_CODE)) =
P_ISS_PCT;
/* Derive Transaction type */
CURSOR TRANS_CUR (P_NAME IN VARCHAR2,P_WH_PCT_ID IN NUMBER,P_ISS_PCT_ID IN NUMBER)
IS
SELECT TRANSACTION_TYPE_ID
FROM APPS.OE_TRANSACTION_TYPES T,
HDG.GEN_PROFITCENTER_MAPPING_TBL M
WHERE T.NAME = REPLACE (SUBSTR (P_NAME, 1, 1),SUBSTR (P_NAME, 1, 1),M.DERIVED_ORDERTYPE)|| SUBSTR (P_NAME, 2)
AND M.WHOLESALER_PROFITCENTER_ID = P_WH_PCT_ID
AND M.ISSUE_PROFITCENTER_ID = P_ISS_PCT_ID;
V_TRANSACTION_TYPE_ID NUMBER;
V_RET_ADJ_HEADER_ID NUMBER;
V_RET_ADJ_LINE_ID NUMBER;
V_CURRENT_LINE_NR NUMBER := 0;
V_ISS_PCT VARCHAR2 (10);
BEGIN
FOR WH_PFT_REC IN WH_PFT_CUR
LOOP
FOR HDR_REC IN HDR_CUR( WH_PFT_REC.WHOLESALER_PC_NAME)
LOOP
IF HDR_REC.ISS_PCT_ID <> HDR_REC.WH_PC_ID
THEN
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST1');
-- CMG_UTILITY_PCK.WRITE_OUTPUT( HDR_REC.ADJUSTMENT_HEADER_ID);
FOR HDR_RET_REC IN HDR_RET_CUR (HDR_REC.ADJUSTMENT_HEADER_ID)
LOOP
V_TRANSACTION_TYPE_ID := NULL;
V_ISS_PCT := NULL;
IF (HDR_RET_REC.WH_PCT_ID <> HDR_RET_REC.ISS_PCT_ID)
THEN
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST2');
SELECT PROFITCENTER
INTO V_ISS_PCT
FROM APPS.CMG_LOGO_MAPPING_VW
WHERE PROFITCENTER_ID = HDR_RET_REC.ISS_PCT_ID;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST3');
-- SELECT TRANSACTION_TYPE_ID
-- INTO V_TRANSACTION_TYPE_ID
-- FROM APPS.OE_TRANSACTION_TYPES T,
-- HDG.GEN_PROFITCENTER_MAPPING_TBL M
-- WHERE --T.NAME = REPLACE(HDR_RET_REC.NAME,SUBSTR(HDR_RET_REC.NAME,1,1),M.DERIVED_ORDERTYPE)
-- T .NAME =
-- REPLACE (SUBSTR (HDR_RET_REC.NAME, 1, 1),
-- SUBSTR (HDR_RET_REC.NAME, 1, 1),
-- M.DERIVED_ORDERTYPE)
-- || SUBSTR (HDR_RET_REC.NAME, 2)
-- AND M.WHOLESALER_PROFITCENTER_ID =
-- HDR_RET_REC.WH_PCT_ID
-- AND M.ISSUE_PROFITCENTER_ID = HDR_RET_REC.ISS_PCT_ID;
-- -- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST4');
-- IF V_TRANSACTION_TYPE_ID IS NULL
-- THEN
-- RAISE NO_DATA_FOUND;
-- END IF;
OPEN TRANS_CUR (HDR_RET_REC.NAME,HDR_RET_REC.WH_PCT_ID,HDR_RET_REC.ISS_PCT_ID);
FETCH TRANS_CUR
INTO V_TRANSACTION_TYPE_ID;
IF TRANS_CUR%NOTFOUND
THEN
CMG_UTILITY_PCK.WRITE_OUTPUT('Transaction Type Setup missing for Tally Nr'||HDR_RET_REC.TALLY_NR);
END IF;
CLOSE TRANS_CUR;
SELECT HDG_HRAH_SEQ.NEXTVAL
INTO V_RET_ADJ_HEADER_ID
FROM DUAL;
INSERT
INTO HDG_RET_ADJ_HEADER_TBL (ADJUSTMENT_HEADER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CUSTOMER_REF_NR,
TALLY_NR,
ADJUSTMENT_FLAG,
RECORD_INTERFACED,
ACCOUNT_NR,
IPDA_MAILBOX_NR,
SOURCE,
TOTAL_COPIES,
CUSTOMER_ID,
CUSTOMER_NAME,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
RETURN_DATE,
PROCESSED_DATE,
EMPLOYEE_ASSIGNED_TO,
ORDER_CATEGORY,
ORDER_TYPE_ID,
VALID_STATUS_FLAG,
DUPLICATE_FLAG,
STORE_NR,
PRICE_LIST_ID,
SALESREP_ID,
SALES_CHANNEL_CODE,
ORIGINAL_ORDER_HEADER_ID,
SOH_ORIGINAL_SYSTEM_REF,
ROAR_THREAD_ID,
PCT_SPLIT_FLAG)
VALUES (V_RET_ADJ_HEADER_ID,
HDR_RET_REC.CREATED_BY,
SYSDATE,
HDR_RET_REC.LAST_UPDATED_BY,
SYSDATE,
HDR_RET_REC.CUSTOMER_REF_NR || '-' || V_ISS_PCT,
HDR_RET_REC.TALLY_NR,
HDR_RET_REC.ADJUSTMENT_FLAG,
'N',
HDR_RET_REC.ACCOUNT_NR,
HDR_RET_REC.IPDA_MAILBOX_NR,
HDR_RET_REC.SOURCE,
HDR_RET_REC.TOT_COPIES,
HDR_RET_REC.CUSTOMER_ID,
HDR_RET_REC.CUSTOMER_NAME,
HDR_RET_REC.BILL_TO_SITE_USE_ID,
HDR_RET_REC.SHIP_TO_SITE_USE_ID,
HDR_RET_REC.RETURN_DATE,
HDR_RET_REC.PROCESSED_DATE,
HDR_RET_REC.EMPLOYEE_ASSIGNED_TO,
HDR_RET_REC.ORDER_CATEGORY,
V_TRANSACTION_TYPE_ID,
HDR_RET_REC.VALID_STATUS_FLAG,
HDR_RET_REC.DUPLICATE_FLAG,
HDR_RET_REC.STORE_NR,
HDR_RET_REC.PRICE_LIST_ID,
HDR_RET_REC.SALESREP_ID,
HDR_RET_REC.SALES_CHANNEL_CODE,
HDR_RET_REC.ORIGINAL_ORDER_HEADER_ID,
HDR_RET_REC.SOH_ORIGINAL_SYSTEM_REF,
HDR_RET_REC.ROAR_THREAD_ID,
'Y');
V_TRANSACTION_TYPE_ID :=NULL;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST5');
FOR HDL_RET_REC
IN HDL_RET_CUR (HDR_RET_REC.ADJUSTMENT_HEADER_ID,
HDR_RET_REC.ISS_PCT_ID,
HDR_RET_REC.WH_PCT_ID)
LOOP
SELECT HDG_HRAL_SEQ.NEXTVAL
INTO V_RET_ADJ_LINE_ID
FROM DUAL;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST6');
-- CMG_UTILITY_PCK.WRITE_OUTPUT(HDL_RET_REC.ADJUSTMENT_LINE_ID);
INSERT
INTO HDG_RET_ADJ_LINES_TBL (
ADJUSTMENT_LINE_ID,
LINE_NR,
ITEM_NR,
QUANTITY,
ADJUSTMENT_FLAG,
CORRECTED_FLAG,
USER_REJECTED_FLAG,
OVERRIDE_FLAG,
LINE_INTERFACED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ADJUSTMENT_HEADER_ID,
BAR_CODE,
BIPAD,
ISBN,
MAGAZINE,
ISSUE_CODE,
UPC,
UNIT_PRICE,
COVER_PRICE,
VALID_STATUS_FLAG,
INVALID_REASON_CODE,
ORIGINAL_ORDER_HEADER_ID,
ORIGINAL_ORDER_LINE_ID,
ORIGINAL_ORDER_LINE_DETAIL_ID,
INVENTORY_ITEM_ID,
WAREHOUSE_ID,
NETSALE_REVALIDATION_LEVEL,
ORIGINAL_RETURN_QUANTITY,
EXPIRATION_STATUS,
RETURN_REASON_CODE,
COVER_DATE_TXT)
VALUES (V_RET_ADJ_LINE_ID,
HDL_RET_REC.LINE_NR,
HDL_RET_REC.ITEM_NR,
HDL_RET_REC.QUANTITY,
HDL_RET_REC.ADJUSTMENT_FLAG,
HDL_RET_REC.CORRECTED_FLAG,
HDL_RET_REC.USER_REJECTED_FLAG,
HDL_RET_REC.OVERRIDE_FLAG,
HDL_RET_REC.LINE_INTERFACED,
HDL_RET_REC.CREATED_BY,
SYSDATE,
HDL_RET_REC.LAST_UPDATED_BY,
SYSDATE,
V_RET_ADJ_HEADER_ID,
HDL_RET_REC.BAR_CODE,
HDL_RET_REC.BIPAD, -- BIPAD
HDL_RET_REC.BIPAD, -- ISBN
HDL_RET_REC.MAGAZINE,
HDL_RET_REC.ISSUE_CODE,
HDL_RET_REC.UPC,
HDL_RET_REC.UNIT_PRICE,
HDL_RET_REC.COVER_PRICE,
HDL_RET_REC.VALID_STATUS_FLAG,
HDL_RET_REC.INVALID_REASON_CODE,
HDL_RET_REC.ORIGINAL_ORDER_HEADER_ID,
HDL_RET_REC.ORIGINAL_ORDER_LINE_ID,
HDL_RET_REC.ORIGINAL_ORDER_LINE_DETAIL_ID,
HDL_RET_REC.INVENTORY_ITEM_ID,
HDL_RET_REC.WAREHOUSE_ID,
HDL_RET_REC.NETSALE_REVALIDATION_LEVEL,
HDL_RET_REC.ORIGINAL_RETURN_QUANTITY,
HDL_RET_REC.EXPIRATION_STATUS,
HDL_RET_REC.RETURN_REASON_CODE,
HDL_RET_REC.COVER_DATE_TXT);
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST7-a');
DELETE FROM HDG_RET_ADJ_LINES_TBL L
WHERE ADJUSTMENT_HEADER_ID =
HDR_RET_REC.ADJUSTMENT_HEADER_ID
AND ADJUSTMENT_LINE_ID =
HDL_RET_REC.ADJUSTMENT_LINE_ID;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST7-b');
END LOOP;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST8');
ELSIF (HDR_RET_REC.WH_PCT_ID = HDR_RET_REC.ISS_PCT_ID)
THEN
--CMG_UTILITY_PCK.WRITE_OUTPUT(HDR_RET_REC.TOT_COPIES);
UPDATE HDG_RET_ADJ_HEADER_TBL
SET TOTAL_COPIES = HDR_RET_REC.TOT_COPIES,
PCT_SPLIT_FLAG = 'Y'
WHERE ADJUSTMENT_HEADER_ID =
HDR_RET_REC.ADJUSTMENT_HEADER_ID;
-- CMG_UTILITY_PCK.WRITE_OUTPUT(' TEST9');
END IF;
END LOOP;
END IF;
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- Cmg_Utility_Pck.write_output ('Transaction Type Setup missing');
-- ROLLBACK;
WHEN OTHERS
THEN
Cmg_Utility_Pck.write_output (SQLERRM);
ROLLBACK;
END GEN_ORDERTYPE_ASSIGN_PRC;
PROCEDURE GEN_DELETE_INVALID_HEADERS_PRC
IS --Genera Foreign Accounts SCR 8385 (delete invalid headers after the split from above procedure )
CURSOR ADJ_HEADER_CUR
IS
SELECT H.ADJUSTMENT_HEADER_ID
FROM HDG.HDG_RET_ADJ_HEADER_TBL H
WHERE RECORD_INTERFACED = 'N'
AND TRUNC (CREATION_DATE) = TRUNC (SYSDATE);
CURSOR ADJ_LINE_CUR (P_HDR_ID IN NUMBER)
IS
SELECT DISTINCT ADJUSTMENT_HEADER_ID
FROM HDG.HDG_RET_ADJ_LINES_TBL L
WHERE L.ADJUSTMENT_HEADER_ID = P_HDR_ID;
V_ADJ_HDR_ID NUMBER := NULL;
BEGIN
FOR ADJ_HEADER_REC IN ADJ_HEADER_CUR
LOOP
OPEN ADJ_LINE_CUR (ADJ_HEADER_REC.ADJUSTMENT_HEADER_ID);
FETCH ADJ_LINE_CUR INTO V_ADJ_HDR_ID;
IF ADJ_LINE_CUR%NOTFOUND
THEN
Cmg_Utility_Pck.write_output (
' Deleted Adjustment Headers'
|| ADJ_HEADER_REC.ADJUSTMENT_HEADER_ID);
DELETE FROM HDG_RET_ADJ_HEADER_TBL
WHERE ADJUSTMENT_HEADER_ID =
ADJ_HEADER_REC.ADJUSTMENT_HEADER_ID;
END IF;
CLOSE ADJ_LINE_CUR;
END LOOP;
END GEN_DELETE_INVALID_HEADERS_PRC;
END HDGRT_DRVR_PCK;
/
No comments:
Post a Comment