SELECT PRH.REQUISITION_HEADER_ID , PRH.SEGMENT1 , PRH.DESCRIPTION , PRH.AUTHORIZATION_STATUS , PLC_AUTH.DISPLAYED_FIELD , APC.CARD_NUMBER , PRH.CREATION_DATE , PRH.TRANSFERRED_TO_OE_FLAG , POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID) , PRH.EMERGENCY_PO_NUM , WN.NOTIFICATION_ID FROM PO_REQUISITION_HEADERS PRH , PO_LOOKUP_CODES PLC_AUTH , AP_CARDS APC , PER_ALL_PEOPLE_F HRE , GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMETERS FSP , WF_NOTIFICATION_ATTRIBUTES WNA , WF_NOTIFICATIONS WN , FND_USER U , FND_USER_RESP_GROUPS UR , FND_RESPONSIBILITY R WHERE U.USER_NAME = FND_GLOBAL.USER_NAME AND U.USER_ID = UR.USER_ID AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND TRUNC(SYSDATE) BETWEEN UR.START_DATE AND NVL(UR.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN U.START_DATE AND NVL(U.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN R.START_DATE AND NVL(R.END_DATE , SYSDATE + 1) AND U.EMPLOYEE_ID IS NOT NULL AND PRH.SEGMENT1 = WNA.TEXT_VALUE AND WNA.NAME = 'DOCUMENT_NUMBER' AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID AND WN.RECIPIENT_ROLE = 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID AND WN.STATUS = 'OPEN' AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE FROM PO_DOCUMENT_TYPES) AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE' , 'PO_REQ_REMINDER1' , 'PO_REQ_APPROVE_WEB_MSG' , 'PO_REQ_REMINDER2' , 'PO_REQ_REMINDER1_WEB' , 'PO_REQ_REMINDER2_WEB') AND PRH.PREPARER_ID = HRE.PERSON_ID AND HRE.EFFECTIVE_START_DATE <= SYSDATE AND HRE.EFFECTIVE_END_DATE >= SYSDATE AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND PRH.PCARD_ID = APC.CARD_ID(+) AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS , 'INCOMPLETE') AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE' UNION ALL /* ROLE RELATED TO GROUP BOX USER */ SELECT PRH.REQUISITION_HEADER_ID , PRH.SEGMENT1 , PRH.DESCRIPTION , PRH.AUTHORIZATION_STATUS , PLC_AUTH.DISPLAYED_FIELD , APC.CARD_NUMBER , PRH.CREATION_DATE , PRH.TRANSFERRED_TO_OE_FLAG , POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID) , PRH.EMERGENCY_PO_NUM , WN.NOTIFICATION_ID FROM PO_REQUISITION_HEADERS PRH , PO_LOOKUP_CODES PLC_AUTH , AP_CARDS APC , PER_ALL_PEOPLE_F HRE , GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMETERS FSP , WF_NOTIFICATION_ATTRIBUTES WNA , WF_NOTIFICATIONS WN , GHR_GROUPBOXES GBX , GHR_GROUPBOX_USERS GBU , FND_USER U WHERE U.USER_NAME = FND_GLOBAL.USER_NAME AND GBU.GROUPBOX_ID = GBX.GROUPBOX_ID AND GBU.USER_NAME = U.USER_NAME AND U.EMPLOYEE_ID IS NOT NULL AND PRH.SEGMENT1 = WNA.TEXT_VALUE AND WNA.NAME = 'DOCUMENT_NUMBER' AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID AND WN.RECIPIENT_ROLE = GBX.NAME AND WN.STATUS = 'OPEN' AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE FROM PO_DOCUMENT_TYPES) AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE' , 'PO_REQ_REMINDER1' , 'PO_REQ_APPROVE_WEB_MSG' , 'PO_REQ_REMINDER2' , 'PO_REQ_REMINDER1_WEB' , 'PO_REQ_REMINDER2_WEB') AND PRH.PREPARER_ID = HRE.PERSON_ID AND HRE.EFFECTIVE_START_DATE <= SYSDATE AND HRE.EFFECTIVE_END_DATE >= SYSDATE AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND PRH.PCARD_ID = APC.CARD_ID(+) AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS , 'INCOMPLETE') AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE' UNION ALL SELECT PRH.REQUISITION_HEADER_ID , PRH.SEGMENT1 , PRH.DESCRIPTION , PRH.AUTHORIZATION_STATUS , PLC_AUTH.DISPLAYED_FIELD , APC.CARD_NUMBER , PRH.CREATION_DATE , PRH.TRANSFERRED_TO_OE_FLAG , POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID) , PRH.EMERGENCY_PO_NUM , WN.NOTIFICATION_ID FROM PO_REQUISITION_HEADERS PRH , PO_LOOKUP_CODES PLC_AUTH , AP_CARDS APC , PER_ALL_PEOPLE_F HRE , GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMETERS FSP , WF_NOTIFICATION_ATTRIBUTES WNA , WF_NOTIFICATIONS WN , WF_LOCAL_USER_ROLES WLUR WHERE WLUR.USER_NAME = FND_GLOBAL.USER_NAME AND PRH.SEGMENT1 = WNA.TEXT_VALUE AND WNA.NAME = 'DOCUMENT_NUMBER' AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID AND WN.RECIPIENT_ROLE = WLUR.ROLE_NAME AND WN.STATUS = 'OPEN' AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE FROM PO_DOCUMENT_TYPES) AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE' , 'PO_REQ_REMINDER1' , 'PO_REQ_APPROVE_WEB_MSG' , 'PO_REQ_REMINDER2' , 'PO_REQ_REMINDER1_WEB' , 'PO_REQ_REMINDER2_WEB') AND PRH.PREPARER_ID = HRE.PERSON_ID AND HRE.EFFECTIVE_START_DATE <= SYSDATE AND HRE.EFFECTIVE_END_DATE >= SYSDATE AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND PRH.PCARD_ID = APC.CARD_ID(+) AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS , 'INCOMPLETE') AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE' UNION ALL SELECT PRH.REQUISITION_HEADER_ID , PRH.SEGMENT1 , PRH.DESCRIPTION , PRH.AUTHORIZATION_STATUS , PLC_AUTH.DISPLAYED_FIELD , APC.CARD_NUMBER , PRH.CREATION_DATE , PRH.TRANSFERRED_TO_OE_FLAG , POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID) , PRH.EMERGENCY_PO_NUM , WN.NOTIFICATION_ID FROM PO_REQUISITION_HEADERS PRH , PO_LOOKUP_CODES PLC_AUTH , AP_CARDS APC , PER_ALL_PEOPLE_F HRE , GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMETERS FSP , WF_NOTIFICATION_ATTRIBUTES WNA , WF_NOTIFICATIONS WN WHERE PRH.SEGMENT1 = WNA.TEXT_VALUE AND WNA.NAME = 'DOCUMENT_NUMBER' AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID AND WN.RECIPIENT_ROLE = FND_GLOBAL.USER_NAME AND WN.STATUS = 'OPEN' AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE FROM PO_DOCUMENT_TYPES) AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE' , 'PO_REQ_REMINDER1' , 'PO_REQ_APPROVE_WEB_MSG' , 'PO_REQ_REMINDER2' , 'PO_REQ_REMINDER1_WEB' , 'PO_REQ_REMINDER2_WEB') AND PRH.PREPARER_ID = HRE.PERSON_ID AND HRE.EFFECTIVE_START_DATE <= SYSDATE AND HRE.EFFECTIVE_END_DATE >= SYSDATE AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND PRH.PCARD_ID = APC.CARD_ID(+) AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS , 'INCOMPLETE') AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE'
APPS.POR_VIEW_REQS_TO_APPR_V
[Home] [Help]