CREATE OR REPLACE PACKAGE BODY APPS.SFI_OM_ORD_CHANGE_REP IS ---------------------------------------------------------------------- -- Author : Vipin Jain -- Date : 30-Jun-2005 -- Description : This Package is created for Order Change Detail Report. -- Created Object(s) Name -- Procedure GEN_REPORT_DATA -- Procedure DEL_REPORT_DATA -- Procedure MAIL_REPORT -- Used Object(s) Name -- Procedure SFI_SEND_MAIL.WITH_ATTACHMENT_AUTOTRANS -- Change History -- Date Author Description ------------------------------------------------------------------------ -- 30-Jun-2006 Vipin Jain Created -- 31-AUG-2006 Vipin Jain Modify ------------------------------------------------------------------------ /*---------------------------------------------------------------------- NAMEING CONVENTION BETWEEN REPROT ,PACKAGE, CONCURRENT PROGRAM -- Proc Description Report V_CUSTOMER_NUMBER Customer Number P_CUST_SITE_NO V_PARTY_SITE_NUM Ship To Site# P_SHIP_TO_SITE_NO V_PERIOD_DATE_CHOICE V_GL_PRIOD Period P_GL_PERIOD V_FROM_DATE Date Range From P_FROM_DATE V_TO_DATE Date Range To P_TO_DATE V_ACCT_MANGAER_ID Account Manager P_ACCT_MANGAER_ID V_DISTRICT_CODE Sales District P_DISTRICT_CODE V_REGION_CODE Sales Region P_REGION_CODE V_REPORT_CODE Report Code P_REPORT_CODE V_BUSINESS_SEGMENT Business Segment P_BUSINESS_SEGMENT ----------------------------------------------------------------------*/ ------------------------------------------------------------------------ -- PROCEDURE GEN_REPORT_DATA WILL FATCH DATA FROM SOME AUDIT VIEW(s) AND -- INSERT ALL RECORD INTO TEMP TABLE ------------------------------------------------------------------------ PROCEDURE GEN_REPORT_DATA ( V_CONC_REQUEST_ID IN NUMBER, V_GL_PRIOD IN GL_PERIODS.PERIOD_NAME%TYPE DEFAULT NULL, V_FROM_DATE IN DATE DEFAULT NULL, V_TO_DATE IN DATE DEFAULT NULL, V_DISTRICT_CODE IN VARCHAR2 DEFAULT NULL, V_REGION_CODE IN VARCHAR2 DEFAULT NULL, V_REPORT_CODE IN VARCHAR2 DEFAULT NULL, V_BUSINESS_SEGMENT IN VARCHAR2 DEFAULT NULL, V_ACCT_MANGAER_ID IN OE_ORDER_HEADERS_ALL.ATTRIBUTE13%TYPE DEFAULT NULL, V_PARTY_SITE_NUM IN HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE DEFAULT NULL, V_CUSTOMER_NUMBER IN HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE DEFAULT NULL ) IS -- -- -- CURSOR FOR FATCH ORDER'S HEADERS RECORDS CURSOR HDR_AUDIT_ATTR_DESC(C_FROM_DATE DATE, C_TO_DATE DATE) IS SELECT AADV.ENTITY_ID, AADV.REASON, AADV.ATTRIBUTE_DISPLAY_NAME, AADV.ENTITY_NUMBER, AADV.ENTITY_DISPLAY_NAME, AADV.NEW_DISPLAY_VALUE, AADV.OLD_DISPLAY_VALUE, AADV.ATTRIBUTE_ID, AADV.ORDER_NUMBER, AADV.HIST_CREATION_DATE, OOH.FLOW_STATUS_CODE FLOW_STATUS_CODE, OOH.HEADER_ID HEADER_ID, OOH.SHIP_TO_ORG_ID, HPS.PARTY_ID PARTY_ID, HPS.PARTY_SITE_NUMBER SITE_NO, HL.ADDRESS1 SITE_NAME, HL.CITY CITY, HL.STATE STATE FROM OE_ORDER_HEADERS_ALL OOH, OE_AUDIT_ATTR_DESC_V AADV, OE_TRANSACTION_TYPES_TL OT, HZ_CUST_ACCOUNTS CUST, HZ_CUST_ACCT_SITES_ALL HCASA, HZ_CUST_SITE_USES_ALL HCSUA, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL WHERE OOH.HEADER_ID = AADV.ENTITY_NUMBER AND OOH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID AND OOH.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID AND CUST.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID AND OOH.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID AND HPS.PARTY_ID = CUST.PARTY_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HL.LOCATION_ID = HPS.LOCATION_ID AND OT.NAME IN ('EDI Order', 'VMI Order', 'Manual Order', 'International', 'Vendor Direct - Intl', 'Back Order-EDI and Manual Only', 'Consignment - Ship Only', 'Triangular - Ship and Invoice', 'Vendor Direct', 'RGB Order') -- --Customer # AND CUST.ACCOUNT_NUMBER = NVL(V_CUSTOMER_NUMBER, CUST.ACCOUNT_NUMBER) --Ship to Site # AND HPS.PARTY_SITE_NUMBER = NVL(V_PARTY_SITE_NUM, HPS.PARTY_SITE_NUMBER) --Account Manager AND OOH.ATTRIBUTE13 = NVL(V_ACCT_MANGAER_ID, OOH.ATTRIBUTE13) --Date Range and gl period --Closed and Canceled Order Check AND UPPER(OOH.FLOW_STATUS_CODE) IN ('CLOSED', 'CANCELLED') AND EXISTS (SELECT 1 FROM OE_ORDER_LINES_ALL OOL WHERE OOL.HEADER_ID = OOH.HEADER_ID AND TRUNC(OOL.ACTUAL_SHIPMENT_DATE) BETWEEN TRUNC(C_FROM_DATE) AND TRUNC(C_TO_DATE)) --District and Region AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_SHIP_TO@PROD SCST WHERE SCST.SALES_MGR = NVL(V_DISTRICT_CODE, SCST.SALES_MGR) AND SCST.TEAM_LEADER = NVL(V_REGION_CODE, SCST.TEAM_LEADER) AND OOH.SALESREP_ID = SCST.SALES_REP_ID) AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_LISTING@PROD SCL, OE_ORDER_LINES_ALL OOL WHERE SCL.REPORT_CODE = NVL(V_REPORT_CODE, SCL.REPORT_CODE) AND SCL.BUSINESS_SEGMENT = NVL(V_BUSINESS_SEGMENT, SCL.BUSINESS_SEGMENT) AND OOH.HEADER_ID = OOL.HEADER_ID AND OOL.INVENTORY_ITEM_ID = SCL.INVENTORY_ITEM_ID) -- AND AADV.ENTITY_ID = 1 AND UPPER(AADV.REASON) = 'CRC_CUSTOMER REQUEST' AND UPPER(AADV.ATTRIBUTE_DISPLAY_NAME) IN ('CUSTOMER PO', 'ORDERED DATE', 'REQUEST DATE', 'CUSTOMER DELIVERY DATE', 'WAREHOUSE', 'FREIGHT TERMS', 'ORDERED QUANTITY', 'NEW LINE ADDED'); -- --BULK COLLECT RECORD DECLARATION FOR HEADERS TYPE BULKREC IS RECORD( CONC_REQUEST_ID NUMBER, SFI_CHANGE_ID NUMBER, ENTITY_ID OE_AUDIT_ATTR_DESC_V.ENTITY_ID%TYPE, ENTITY_DISPLAY_NAME OE_AUDIT_ATTR_DESC_V.ENTITY_DISPLAY_NAME%TYPE, ATTRIBUTE_ID OE_AUDIT_ATTR_DESC_V.ATTRIBUTE_ID%TYPE, ATTRIBUTE_DISPLAY_NAME OE_AUDIT_ATTR_DESC_V.ATTRIBUTE_DISPLAY_NAME%TYPE, ORDER_NUMBER OE_AUDIT_ATTR_DESC_V.ORDER_NUMBER%TYPE, ENTITY_NUMBER OE_AUDIT_ATTR_DESC_V.ENTITY_NUMBER%TYPE, HIST_CREATION_DATE OE_AUDIT_ATTR_DESC_V.HIST_CREATION_DATE%TYPE, SHIP_TO_ORG_ID OE_ORDER_HEADERS_ALL.SHIP_TO_ORG_ID%TYPE, SFI_CS_MANAGER_ID NUMBER, ATTR_CHANGE_VALUE NUMBER, REJECT_FLAG VARCHAR2(2), PARTY_ID HZ_PARTY_SITES.PARTY_ID%TYPE, SHIP_TO_SITE_NO HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE, SHIP_TO_SITE_NAME HZ_LOCATIONS.ADDRESS1%TYPE, SHIP_TO_CITY HZ_LOCATIONS.CITY%TYPE, SHIP_TO_STATE HZ_LOCATIONS.STATE%TYPE, CREATION_DATE DATE, CREATED_BY NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, ATTRIBUTE1 VARCHAR2(240), ATTRIBUTE2 VARCHAR2(240), ATTRIBUTE3 VARCHAR2(240), ATTRIBUTE4 VARCHAR2(240), ATTRIBUTE5 VARCHAR2(240)); TYPE HDRRECTAB IS TABLE OF BULKREC INDEX BY BINARY_INTEGER; HDRECS HDRRECTAB; V_ARRAY_SIZE NUMBER := 1200; -- TYPE HDR_REC IS TABLE OF HDR_AUDIT_ATTR_DESC%ROWTYPE INDEX BY BINARY_INTEGER; HDR HDR_REC; -- -- -- -- --CURSOR FOR FATCH ITEM'S RECORDS CURSOR LINE_AUDIT_ATTR_DESC(C_FROM_DATE DATE, C_TO_DATE DATE) IS SELECT AADV.ENTITY_ID, AADV.REASON, AADV.ATTRIBUTE_DISPLAY_NAME, AADV.ENTITY_NUMBER, AADV.ENTITY_DISPLAY_NAME, AADV.NEW_DISPLAY_VALUE, AADV.OLD_DISPLAY_VALUE, AADV.ATTRIBUTE_ID, AADV.ORDER_NUMBER, AADV.HIST_CREATION_DATE, OOH.FLOW_STATUS_CODE FLOW_STATUS_CODE, OOH.HEADER_ID HEADER_ID, OOH.SHIP_TO_ORG_ID, HPS.PARTY_ID PARTY_ID, HPS.PARTY_SITE_NUMBER SITE_NO, HL.ADDRESS1 SITE_NAME, HL.CITY CITY, HL.STATE STATE FROM OE_ORDER_HEADERS_ALL OOH, OE_AUDIT_ATTR_DESC_V AADV, OE_ORDER_LINES_ALL OOL, OE_TRANSACTION_TYPES_TL OT, HZ_CUST_ACCOUNTS CUST, HZ_CUST_ACCT_SITES_ALL HCASA, HZ_CUST_SITE_USES_ALL HCSUA, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL WHERE OOH.HEADER_ID = OOL.HEADER_ID AND OOL.LINE_ID = AADV.ENTITY_NUMBER AND OOH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID AND UPPER(OOH.FLOW_STATUS_CODE) IN ('CANCELLED', 'CLOSED') AND OOH.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID AND CUST.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID AND OOH.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID AND HPS.PARTY_ID = CUST.PARTY_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HL.LOCATION_ID = HPS.LOCATION_ID AND OT.NAME IN ('EDI Order', 'VMI Order', 'Manual Order', 'International', 'Vendor Direct - Intl', 'Back Order-EDI and Manual Only', 'Consignment - Ship Only', 'Triangular - Ship and Invoice', 'Vendor Direct', 'RGB Order') AND OOL.FLOW_STATUS_CODE IN ('CLOSED', 'CANCELLED') AND TRUNC(OOL.ACTUAL_SHIPMENT_DATE) BETWEEN TRUNC(C_FROM_DATE) AND TRUNC(C_TO_DATE) -- --Customer # AND CUST.ACCOUNT_NUMBER = NVL(V_CUSTOMER_NUMBER, CUST.ACCOUNT_NUMBER) --Ship to Site # AND HPS.PARTY_SITE_NUMBER = NVL(V_PARTY_SITE_NUM, HPS.PARTY_SITE_NUMBER) --Account Manager AND OOH.ATTRIBUTE13 = NVL(V_ACCT_MANGAER_ID, OOH.ATTRIBUTE13) --Date Range and gl period --Closed and Canceled Order Check --District and Region AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_SHIP_TO@PROD SCST WHERE SCST.SALES_MGR = NVL(V_DISTRICT_CODE, SCST.SALES_MGR) AND SCST.TEAM_LEADER = NVL(V_REGION_CODE, SCST.TEAM_LEADER) AND OOH.SALESREP_ID = SCST.SALES_REP_ID) AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_LISTING@PROD SCL WHERE SCL.REPORT_CODE = NVL(V_REPORT_CODE, SCL.REPORT_CODE) AND SCL.BUSINESS_SEGMENT = NVL(V_BUSINESS_SEGMENT, SCL.BUSINESS_SEGMENT) --AND ooh.header_id = ool.header_id AND OOL.INVENTORY_ITEM_ID = SCL.INVENTORY_ITEM_ID) -- AND AADV.ENTITY_ID = 2 AND AADV.REASON = 'CRC_CUSTOMER REQUEST' AND UPPER(AADV.ATTRIBUTE_DISPLAY_NAME) IN ('CUSTOMER PO', 'ORDERED DATE', 'REQUEST DATE', 'CUSTOMER DELIVERY DATE', 'WAREHOUSE', 'FREIGHT TERMS', 'ORDERED QUANTITY', 'NEW LINE ADDED'); -- --BULK COLLECT RECORD DECLARATION FOR ITEM'S TYPE LINEREC IS RECORD( CONC_REQUEST_ID NUMBER, SFI_CHANGE_ID NUMBER, ENTITY_ID OE_AUDIT_ATTR_DESC_V.ENTITY_ID%TYPE, ENTITY_DISPLAY_NAME OE_AUDIT_ATTR_DESC_V.ENTITY_DISPLAY_NAME%TYPE, ATTRIBUTE_ID OE_AUDIT_ATTR_DESC_V.ATTRIBUTE_ID%TYPE, ATTRIBUTE_DISPLAY_NAME OE_AUDIT_ATTR_DESC_V.ATTRIBUTE_DISPLAY_NAME%TYPE, ORDER_NUMBER OE_AUDIT_ATTR_DESC_V.ORDER_NUMBER%TYPE, ENTITY_NUMBER OE_AUDIT_ATTR_DESC_V.ENTITY_NUMBER%TYPE, HIST_CREATION_DATE OE_AUDIT_ATTR_DESC_V.HIST_CREATION_DATE%TYPE, SHIP_TO_ORG_ID OE_ORDER_HEADERS_ALL.SHIP_TO_ORG_ID%TYPE, SFI_CS_MANAGER_ID NUMBER, ATTR_CHANGE_VALUE NUMBER, REJECT_FLAG VARCHAR2(2), PARTY_ID HZ_PARTY_SITES.PARTY_ID%TYPE, SHIP_TO_SITE_NO HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE, SHIP_TO_SITE_NAME HZ_LOCATIONS.ADDRESS1%TYPE, SHIP_TO_CITY HZ_LOCATIONS.CITY%TYPE, SHIP_TO_STATE HZ_LOCATIONS.STATE%TYPE, CREATION_DATE DATE, CREATED_BY NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, ATTRIBUTE1 VARCHAR2(240), ATTRIBUTE2 VARCHAR2(240), ATTRIBUTE3 VARCHAR2(240), ATTRIBUTE4 VARCHAR2(240), ATTRIBUTE5 VARCHAR2(240)); TYPE LINERECTAB IS TABLE OF LINEREC INDEX BY BINARY_INTEGER; LINERECS LINERECTAB; -- TYPE LINE_REC IS TABLE OF LINE_AUDIT_ATTR_DESC%ROWTYPE INDEX BY BINARY_INTEGER; LINE LINE_REC; -- -- -- -- -- Declare the cursor for count the total for the report CURSOR C_TOT_COUNT(C_FROM_DATE DATE, C_TO_DATE DATE) IS SELECT OOH.HEADER_ID, OOH.ORDER_NUMBER, OOL.LINE_ID, OOH.FLOW_STATUS_CODE OOH_FLOW_STATUS_CODE, OOH.SHIP_TO_ORG_ID OOH_SHIP_TO_ORG_ID, OOL.FLOW_STATUS_CODE OOL_FLOW_STATUS_CODE FROM OE_ORDER_HEADERS_ALL OOH, OE_ORDER_LINES_ALL OOL, OE_TRANSACTION_TYPES_TL OT, HZ_CUST_ACCT_SITES_ALL HCASA, HZ_CUST_SITE_USES_ALL HCSUA, HZ_CUST_ACCOUNTS CUST, HZ_PARTY_SITES HPS WHERE OOL.HEADER_ID = OOH.HEADER_ID AND UPPER(OOH.FLOW_STATUS_CODE) IN ('CANCELLED', 'CLOSED') AND OOH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID AND OOH.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID AND CUST.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HPS.PARTY_ID = CUST.PARTY_ID AND OOH.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID -- --Customer # AND CUST.ACCOUNT_NUMBER = NVL(V_CUSTOMER_NUMBER, CUST.ACCOUNT_NUMBER) --Ship to Site # AND HPS.PARTY_SITE_NUMBER = NVL(V_PARTY_SITE_NUM, HPS.PARTY_SITE_NUMBER) --Account Manager AND OOH.ATTRIBUTE13 = NVL(V_ACCT_MANGAER_ID, OOH.ATTRIBUTE13) --Date Range & gl period --Closed & Canceled Order Check AND UPPER(OOL.FLOW_STATUS_CODE) IN ('CLOSED', 'CANCELLED') AND TRUNC(OOL.ACTUAL_SHIPMENT_DATE) BETWEEN TRUNC(C_FROM_DATE) AND TRUNC(C_TO_DATE) AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_SHIP_TO@PROD SCST WHERE SCST.SALES_MGR = NVL(V_DISTRICT_CODE, SCST.SALES_MGR) AND SCST.TEAM_LEADER = NVL(V_REGION_CODE, SCST.TEAM_LEADER) AND OOH.SALESREP_ID = SCST.SALES_REP_ID) AND EXISTS (SELECT 1 FROM SFI_CUSTOMER_LISTING@PROD SCL WHERE SCL.REPORT_CODE = NVL(V_REPORT_CODE, SCL.REPORT_CODE) AND SCL.BUSINESS_SEGMENT = NVL(V_BUSINESS_SEGMENT, SCL.BUSINESS_SEGMENT) AND SCL.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID) --AND scl.ship_to_org_id = ooh.ship_to_org_id) AND OT.NAME IN ('EDI Order', 'VMI Order', 'Manual Order', 'International', 'Vendor Direct - Intl', 'Back Order-EDI and Manual Only', 'Consignment - Ship Only', 'Triangular - Ship and Invoice', 'Vendor Direct', 'RGB Order'); -- -- Declare the type record for the TOTAL COUNT CURSOR TYPE COUNT_REC IS RECORD( CONC_REQUEST_ID NUMBER, HEADER_ID NUMBER, ORDER_NUMBER NUMBER, LINE_ID NUMBER, OOH_FLOW_STATUS_CODE VARCHAR2(30), OOH_SHIP_TO_ORG_ID NUMBER, OOL_FLOW_STATUS_CODE VARCHAR2(30), CREATION_DATE DATE, CREATED_BY NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER); -- TYPE TOT_COUNT_REC IS TABLE OF COUNT_REC INDEX BY BINARY_INTEGER; TOTAL_COUNT_REC TOT_COUNT_REC; V_ARRAY_SIZE NUMBER := 7200; -- TYPE TOTL_COUNT IS TABLE OF C_TOT_COUNT%ROWTYPE INDEX BY BINARY_INTEGER; T_COUNT TOTL_COUNT; -- -- -- -- --Variable Declaration V_CHANGE_ID NUMBER := 0; V_PERIOD_START_DATE DATE; V_PERIOD_END_DATE DATE; V_LAST_UPD_DATE DATE; V_ACTUAL_INVOICE_DATE DATE; V_ACTUAL_SHIP_DATE OE_ORDER_LINES_ALL.ACTUAL_SHIPMENT_DATE%TYPE; V_ATTR_CHANGE_VALUE APPS.SFI_OM_ORDER_CHANGE_GLB_TEMP2.ATTR_CHANGE_VALUE%TYPE; -- APPS.SFI_OM_ORDER_GLOBAL_TEMP.ATTR_CHANGE_VALUE%TYPE; --SFIOM.SFI_OM_ORDER_CHANGE_TEMP.ATTR_CHANGE_VALUE%TYPE; -- -- PRAGMA AUTONOMOUS_TRANSACTION; -- -- -- -- BEGIN --Select Start date and End date on the bases of GL Period IF V_GL_PRIOD IS NOT NULL THEN BEGIN SELECT START_DATE, END_DATE INTO V_PERIOD_START_DATE, V_PERIOD_END_DATE FROM GL_PERIODS WHERE UPPER(PERIOD_SET_NAME) = 'SFI_FISCAL_YEAR' AND PERIOD_NAME = V_GL_PRIOD; EXCEPTION WHEN OTHERS THEN V_PERIOD_START_DATE := NULL; V_PERIOD_END_DATE := NULL; END; END IF; --FATCH ORDER'S RECORDS FROM CURSOR AND INSERT INTO TABLE /*FOR V_HDR_AUDIT_ATTR_DESC IN HDR_AUDIT_ATTR_DESC(NVL(V_FROM_DATE, V_PERIOD_START_DATE), NVL(V_TO_DATE, V_PERIOD_END_DATE)) LOOP*/ OPEN HDR_AUDIT_ATTR_DESC(NVL(V_FROM_DATE, V_PERIOD_START_DATE), NVL(V_TO_DATE, V_PERIOD_END_DATE)); LOOP FETCH HDR_AUDIT_ATTR_DESC BULK COLLECT INTO HDR LIMIT 1200; EXIT WHEN HDR_AUDIT_ATTR_DESC%NOTFOUND; END LOOP; CLOSE HDR_AUDIT_ATTR_DESC; --Calculate value for Lead Time /*IF UPPER(V_HDR_AUDIT_ATTR_DESC.ATTRIBUTE_DISPLAY_NAME) ='REQUEST DATE' AND UPPER(V_HDR_AUDIT_ATTR_DESC.ENTITY_DISPLAY_NAME) = 'ORDER HEADER' */ FOR I IN 1 .. HDR.COUNT LOOP HDRECS(I).CONC_REQUEST_ID := V_CONC_REQUEST_ID; --HDRECS(I).SFI_CHANGE_ID HDRECS(I).ENTITY_ID := HDR(I).ENTITY_ID; HDRECS(I).ENTITY_DISPLAY_NAME := HDR(I).ENTITY_DISPLAY_NAME; HDRECS(I).ATTRIBUTE_ID := HDR(I).ATTRIBUTE_ID; HDRECS(I).ATTRIBUTE_DISPLAY_NAME := HDR(I).ATTRIBUTE_DISPLAY_NAME; HDRECS(I).ORDER_NUMBER := HDR(I).ORDER_NUMBER; HDRECS(I).ENTITY_NUMBER := HDR(I).ENTITY_NUMBER; HDRECS(I).HIST_CREATION_DATE := HDR(I).HIST_CREATION_DATE; HDRECS(I).SHIP_TO_ORG_ID := HDR(I).SHIP_TO_ORG_ID; HDRECS(I).SFI_CS_MANAGER_ID := NULL; --ATTR_CHANGE_VALUE HDRECS(I).REJECT_FLAG := NULL; HDRECS(I).PARTY_ID := HDR(I).PARTY_ID; HDRECS(I).SHIP_TO_SITE_NO := HDR(I).SITE_NO; HDRECS(I).SHIP_TO_SITE_NAME := HDR(I).SITE_NAME; HDRECS(I).SHIP_TO_CITY := HDR(I).CITY; HDRECS(I).SHIP_TO_STATE := HDR(I).STATE; HDRECS(I).CREATION_DATE := SYSDATE; HDRECS(I).CREATED_BY := GV_USER_ID; HDRECS(I).LAST_UPDATE_DATE := SYSDATE; HDRECS(I).LAST_UPDATED_BY := GV_USER_ID; IF UPPER(HDR(I).ATTRIBUTE_DISPLAY_NAME) = 'REQUEST DATE' AND UPPER(HDR(I).ENTITY_DISPLAY_NAME) = 'ORDER HEADER' THEN --BEGIN /*SELECT TO_DATE(SUBSTR(V_HDR_AUDIT_ATTR_DESC.NEW_DISPLAY_VALUE,1,11),'DD-MON-YYYY') - TO_DATE(SUBSTR(V_HDR_AUDIT_ATTR_DESC.OLD_DISPLAY_VALUE,1,11),'DD-MON-YYYY')*/ HDRECS(I).ATTR_CHANGE_VALUE := TO_DATE(SUBSTR(HDR(I) .NEW_DISPLAY_VALUE, 1, 11), 'DD-MON-YYYY') - TO_DATE(SUBSTR(HDR(I) .OLD_DISPLAY_VALUE, 1, 11), 'DD-MON-YYYY'); /*SELECT TO_DATE(SUBSTR(HDR(I).NEW_DISPLAY_VALUE,1,11),'DD-MON-YYYY') - TO_DATE(SUBSTR(HDR(I).OLD_DISPLAY_VALUE,1,11),'DD-MON-YYYY') INTO HDRECS(I).ATTR_CHANGE_VALUE FROM DUAL; EXCEPTION WHEN OTHERS THEN HDRECS(I).ATTR_CHANGE_VALUE := NULL; END;*/ ELSE HDRECS(I).ATTR_CHANGE_VALUE := NULL; END IF; --Insert record into custom table for report execution --BEGIN V_CHANGE_ID := V_CHANGE_ID + 1; HDRECS(I).SFI_CHANGE_ID := V_CHANGE_ID; END LOOP; FORALL I IN 1 .. HDRECS.COUNT INSERT INTO APPS.SFI_OM_ORDER_CHANGE_GLB_TEMP2 --APPS.SFI_OM_ORDER_GLOBAL_TEMP VALUES HDRECS (I); /* (CONC_REQUEST_ID, SFI_CHANGE_ID, ENTITY_ID, ENTITY_DISPLAY_NAME, ATTRIBUTE_ID, ATTRIBUTE_DISPLAY_NAME, ORDER_NUMBER, ENTITY_NUMBER, HIST_CREATION_DATE, SHIP_TO_ORG_ID, SFI_CS_MANAGER_ID, ATTR_CHANGE_VALUE, REJECT_FLAG, SHIP_TO_SITE_NO, SHIP_TO_SITE_NAME, SHIP_TO_CITY, SHIP_TO_STATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) VALUES (V_CONC_REQUEST_ID, V_CHANGE_ID, HDRRECS_DESC.ENTITY_ID, HDRRECS.ENTITY_DISPLAY_NAME, HDRRECS.ATTRIBUTE_ID, HDRRECS.ATTRIBUTE_DISPLAY_NAME, HDRRECS.ORDER_NUMBER, HDRRECS.ENTITY_NUMBER, HDRRECS.HIST_CREATION_DATE, HDRRECS.SHIP_TO_ORG_ID, NULL, V_ATTR_CHANGE_VALUE, NULL, HDRRECS.SITE_NO, HDRRECS.SITE_NAME, HDRRECS.CITY, HDRRECS.STATE, SYSDATE, GV_USER_ID, SYSDATE, GV_USER_ID); EXCEPTION WHEN OTHERS THEN NULL; END;*/ -- -- -- -- -- -- FATCH ITEM'S RECORDS FROM CURSOR AND INSERT INTO TABLE -- /*FOR V_LINE_AUDIT_ATTR_DESC IN LINE_AUDIT_ATTR_DESC(NVL(V_FROM_DATE, V_PERIOD_START_DATE), NVL(V_TO_DATE, V_PERIOD_END_DATE)) LOOP*/ -- -- -- -- OPEN LINE_AUDIT_ATTR_DESC(NVL(V_FROM_DATE, V_PERIOD_START_DATE), NVL(V_TO_DATE, V_PERIOD_END_DATE)); LOOP FETCH LINE_AUDIT_ATTR_DESC BULK COLLECT INTO LINE LIMIT 1200; EXIT WHEN LINE_AUDIT_ATTR_DESC%NOTFOUND; END LOOP; CLOSE LINE_AUDIT_ATTR_DESC; --Calculate value for Lead Time /*IF UPPER(V_LINE_AUDIT_ATTR_DESC.ATTRIBUTE_DISPLAY_NAME) ='REQUEST DATE' AND UPPER(V_LINE_AUDIT_ATTR_DESC.ENTITY_DISPLAY_NAME) = 'ORDER HEADER' */ FOR I IN 1 .. LINE.COUNT LOOP LINERECS(I).CONC_REQUEST_ID := V_CONC_REQUEST_ID; --HDRECS(I).SFI_CHANGE_ID LINERECS(I).ENTITY_ID := LINE(I).ENTITY_ID; LINERECS(I).ENTITY_DISPLAY_NAME := LINE(I).ENTITY_DISPLAY_NAME; LINERECS(I).ATTRIBUTE_ID := LINE(I).ATTRIBUTE_ID; LINERECS(I).ATTRIBUTE_DISPLAY_NAME := LINE(I) .ATTRIBUTE_DISPLAY_NAME; LINERECS(I).ORDER_NUMBER := LINE(I).ORDER_NUMBER; LINERECS(I).ENTITY_NUMBER := LINE(I).ENTITY_NUMBER; LINERECS(I).HIST_CREATION_DATE := LINE(I).HIST_CREATION_DATE; LINERECS(I).SHIP_TO_ORG_ID := LINE(I).SHIP_TO_ORG_ID; LINERECS(I).SFI_CS_MANAGER_ID := NULL; --ATTR_CHANGE_VALUE LINERECS(I).REJECT_FLAG := NULL; LINERECS(I).PARTY_ID := LINE(I).PARTY_ID; LINERECS(I).SHIP_TO_SITE_NO := LINE(I).SITE_NO; LINERECS(I).SHIP_TO_SITE_NAME := LINE(I).SITE_NAME; LINERECS(I).SHIP_TO_CITY := LINE(I).CITY; LINERECS(I).SHIP_TO_STATE := LINE(I).STATE; LINERECS(I).CREATION_DATE := SYSDATE; LINERECS(I).CREATED_BY := GV_USER_ID; LINERECS(I).LAST_UPDATE_DATE := SYSDATE; LINERECS(I).LAST_UPDATED_BY := GV_USER_ID; IF UPPER(LINE(I).ATTRIBUTE_DISPLAY_NAME) = 'REQUEST DATE' AND UPPER(LINE(I).ENTITY_DISPLAY_NAME) = 'ORDER HEADER' THEN -- BEGIN /* SELECT TO_DATE(SUBSTR(V_LINE_AUDIT_ATTR_DESC.NEW_DISPLAY_VALUE, 1,11),'DD-MON-YYYY') - TO_DATE(SUBSTR(V_LINE_AUDIT_ATTR_DESC.OLD_DISPLAY_VALUE, 1,11),'DD-MON-YYYY') */ LINERECS(I).ATTR_CHANGE_VALUE := TO_DATE(SUBSTR(LINE(I) .NEW_DISPLAY_VALUE, 1, 11), 'DD-MON-YYYY') - TO_DATE(SUBSTR(LINE(I) .OLD_DISPLAY_VALUE, 1, 11), 'DD-MON-YYYY'); /* SELECT TO_DATE(SUBSTR(LINE(I).NEW_DISPLAY_VALUE, 1,11),'DD-MON-YYYY') - TO_DATE(SUBSTR(LINE(I).OLD_DISPLAY_VALUE, 1,11),'DD-MON-YYYY') INTO LINERECS(I).ATTR_CHANGE_VALUE FROM DUAL; EXCEPTION WHEN OTHERS THEN LINERECS(I).ATTR_CHANGE_VALUE := NULL; END;*/ ELSE LINERECS(I).ATTR_CHANGE_VALUE := NULL; END IF; --Insert record into custom table for report execution --BEGIN V_CHANGE_ID := V_CHANGE_ID + 1; LINERECS(I).SFI_CHANGE_ID := V_CHANGE_ID; END LOOP; FORALL I IN 1 .. LINERECS.COUNT INSERT INTO APPS.SFI_OM_ORDER_CHANGE_GLB_TEMP2 --APPS.SFI_OM_ORDER_GLOBAL_TEMP VALUES LINERECS (I); /*INSERT INTO apps.SFI_OM_ORDER_GLOBAL_TEMP (CONC_REQUEST_ID, SFI_CHANGE_ID, ENTITY_ID, ENTITY_DISPLAY_NAME, ATTRIBUTE_ID, ATTRIBUTE_DISPLAY_NAME, ORDER_NUMBER, ENTITY_NUMBER, HIST_CREATION_DATE, SHIP_TO_ORG_ID, SFI_CS_MANAGER_ID, ATTR_CHANGE_VALUE, REJECT_FLAG, SHIP_TO_SITE_NO, SHIP_TO_SITE_NAME, SHIP_TO_CITY, SHIP_TO_STATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) VALUES (V_CONC_REQUEST_ID, V_CHANGE_ID, LINERECS.ENTITY_ID, LINERECS.ENTITY_DISPLAY_NAME, LINERECS.ATTRIBUTE_ID, LINERECS.ATTRIBUTE_DISPLAY_NAME, LINERECS.ORDER_NUMBER, LINERECS.ENTITY_NUMBER, LINERECS.HIST_CREATION_DATE, LINERECS.SHIP_TO_ORG_ID, NULL, V_ATTR_CHANGE_VALUE, NULL, LINERECS.SITE_NO, LINERECS.SITE_NAME, LINERECS.CITY, LINERECS.STATE, SYSDATE, GV_USER_ID, SYSDATE, GV_USER_ID); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP;*/ -- -- -- -- -- FATCH THE TOTAL COUNT RECORD FROM CURSOR OPEN C_TOT_COUNT(NVL(V_FROM_DATE, V_PERIOD_START_DATE), NVL(V_TO_DATE, V_PERIOD_END_DATE)); LOOP FETCH C_TOT_COUNT BULK COLLECT INTO T_COUNT LIMIT 7200; EXIT WHEN C_TOT_COUNT%NOTFOUND; END LOOP; CLOSE C_TOT_COUNT; -- FOR I IN 1 .. T_COUNT.COUNT LOOP TOTAL_COUNT_REC(I).CONC_REQUEST_ID := V_CONC_REQUEST_ID; TOTAL_COUNT_REC(I).HEADER_ID := T_COUNT(I).HEADER_ID; TOTAL_COUNT_REC(I).ORDER_NUMBER := T_COUNT(I).ORDER_NUMBER; TOTAL_COUNT_REC(I).LINE_ID := T_COUNT(I).LINE_ID; TOTAL_COUNT_REC(I).OOH_FLOW_STATUS_CODE := T_COUNT(I) .OOH_FLOW_STATUS_CODE; TOTAL_COUNT_REC(I).OOH_SHIP_TO_ORG_ID := T_COUNT(I) .OOH_SHIP_TO_ORG_ID; TOTAL_COUNT_REC(I).OOL_FLOW_STATUS_CODE := T_COUNT(I) .OOL_FLOW_STATUS_CODE; TOTAL_COUNT_REC(I).CREATION_DATE := SYSDATE; TOTAL_COUNT_REC(I).CREATED_BY := GV_USER_ID; TOTAL_COUNT_REC(I).LAST_UPDATE_DATE := SYSDATE; TOTAL_COUNT_REC(I).LAST_UPDATED_BY := GV_USER_ID; END LOOP; FORALL I IN 1 .. TOTAL_COUNT_REC.COUNT INSERT INTO APPS.SFI_OM_ORDER_CHANGE_GLB_TMP --APPS.SFI_OM_ORDER_GLOBAL_TEMP VALUES TOTAL_COUNT_REC (I); -- -- COMMIT; -- -- -- -- EXCEPTION WHEN OTHERS THEN NULL; END GEN_REPORT_DATA; ------------------------------------------------------------------------ -- PROCEDURE DEL_REPORT_DATA WILL DELETE ALL RECORD FROM TEMP TABLE ------------------------------------------------------------------------ PROCEDURE DEL_REPORT_DATA(V_CONC_REQUEST_ID IN NUMBER) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN DELETE FROM APPS.SFI_OM_ORDER_CHANGE_GLB_TEMP2 --SFIOM.SFI_OM_ORDER_CHANGE_TEMP WHERE CONC_REQUEST_ID = V_CONC_REQUEST_ID; -- COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END DEL_REPORT_DATA; ------------------------------------------------------------------------ -- PROCEDURE MAIL_REPORT WILL SEND REPORT OUTPUT BY EMAIL. ------------------------------------------------------------------------ PROCEDURE MAIL_REPORT ( ERRBUFF OUT VARCHAR2, RETCODE OUT VARCHAR2, V_CUSTOMER_NUMBER IN HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE DEFAULT NULL, V_PARTY_SITE_NUM IN HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE DEFAULT NULL, V_PERIOD_DATE_CHOICE IN VARCHAR2 DEFAULT NULL, V_GL_PRIOD IN GL_PERIODS.PERIOD_NAME%TYPE DEFAULT NULL, V_FROM_DATE IN VARCHAR2 DEFAULT NULL, V_TO_DATE IN VARCHAR2 DEFAULT NULL, V_ACCT_MANGAER_ID IN OE_ORDER_HEADERS_ALL.ATTRIBUTE13%TYPE DEFAULT NULL, V_DISTRICT_CODE IN VARCHAR2 DEFAULT NULL, V_REGION_CODE IN VARCHAR2 DEFAULT NULL, V_REPORT_CODE IN VARCHAR2 DEFAULT NULL, V_BUSINESS_SEGMENT IN VARCHAR2 DEFAULT NULL ) IS --Variable Declaration V_REQUEST_NO NUMBER; V_REQUEST_NUM NUMBER; V_EMAIL_ADDR VARCHAR2(500) := NULL; L_WAIT_FOR_REQ BOOLEAN := FALSE; L_PHASE VARCHAR2(30); L_STATUS VARCHAR2(30); L_DEV_PHASE VARCHAR2(30); L_DEV_STATUS VARCHAR2(30); L_MESSAGE VARCHAR2(1000); PRAGMA AUTONOMOUS_TRANSACTION; BEGIN V_REQUEST_NO := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'XXSFIOM', PROGRAM => 'SFOMOCHR', START_TIME => NULL, SUB_REQUEST => FALSE, ARGUMENT1 => V_CUSTOMER_NUMBER, ARGUMENT2 => V_PARTY_SITE_NUM, ARGUMENT3 => V_GL_PRIOD, ARGUMENT4 => TO_DATE(V_FROM_DATE, 'DD-MON-RR'), ARGUMENT5 => TO_DATE(V_TO_DATE, 'DD-MON-RR'), ARGUMENT6 => V_ACCT_MANGAER_ID, ARGUMENT7 => V_DISTRICT_CODE, ARGUMENT8 => V_REGION_CODE, ARGUMENT9 => V_REPORT_CODE, ARGUMENT10 => V_BUSINESS_SEGMENT, ARGUMENT11 => FND_GLOBAL.LOCAL_CHR(0)); COMMIT; -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Report is submitted and the request # is ' || V_REQUEST_NO); -- -- IF V_REQUEST_NO = 0 THEN L_WAIT_FOR_REQ := FALSE; ELSE L_WAIT_FOR_REQ := FND_CONCURRENT.WAIT_FOR_REQUEST(V_REQUEST_NO, 10, 0, L_PHASE, L_STATUS, L_DEV_PHASE, L_DEV_STATUS, L_MESSAGE); END IF; -- IF L_WAIT_FOR_REQ THEN --Fatch email address BEGIN SELECT DISTINCT EMAIL_ADDRESS INTO V_EMAIL_ADDR FROM FND_USER WHERE USER_ID = GV_USER_ID AND ROWNUM <= 1; EXCEPTION WHEN OTHERS THEN V_EMAIL_ADDR := NULL; END; --If email address is not null then only CALL THE PROGRAM IF V_EMAIL_ADDR IS NOT NULL THEN V_REQUEST_NUM := SFI_SEND_MAIL.WITH_ATTACHMENT_AUTOTRANS(V_REQUEST_NO, V_EMAIL_ADDR, 'OM ORDER CHANGE REPORT'); -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Report output is sent by email to ' || V_EMAIL_ADDR); -- -- COMMIT; -- END IF; -- -- --Call proc it will delete data from temp table /* BEGIN -- DEL_REPORT_DATA(V_CONC_REQUEST_ID => V_REQUEST_NO); -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delete the record from custom table.'); -- EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occured in delete the record from custom table.'); END; */ -- -- END IF; EXCEPTION WHEN OTHERS THEN NULL; END MAIL_REPORT; END SFI_OM_ORD_CHANGE_REP; /