001: /*
002: * Copyright 2005 Sun Microsystems, Inc. All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions
006: * are met:
007: *
008: * - Redistributions of source code must retain the above copyright
009: * notice, this list of conditions and the following disclaimer.
010: *
011: * - Redistribution in binary form must reproduce the above copyright
012: * notice, this list of conditions and the following disclaimer in
013: * the documentation and/or other materials provided with the
014: * distribution.
015: *
016: * Neither the name of Sun Microsystems, Inc. or the names of
017: * contributors may be used to endorse or promote products derived
018: * from this software without specific prior written permission.
019: *
020: * This software is provided "AS IS," without a warranty of any
021: * kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND
022: * WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY,
023: * FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY
024: * EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES
025: * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
026: * DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN
027: * OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
028: * FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR
029: * PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
030: * LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE,
031: * EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
032: *
033: * You acknowledge that Software is not designed, licensed or intended
034: * for use in the design, construction, operation or maintenance of
035: * any nuclear facility.
036: */
037:
038: package com.sun.portal.oracleportlet.util;
039:
040: /**
041: * This interface declares all constants used by Oracle Apps portlets
042: *
043: * @author Zahid Syed
044: */
045:
046: public interface OraclePortletConstants {
047:
048: /* JSPs used by portlets */
049: public static final String REQUISITION_INFO_JSP = "/jsps/RequisitionInformation.jsp";
050: public static final String REQUISITION_DETAILS_JSP = "/jsps/RequisitionDetails.jsp";
051: public static final String APPROVAL_HEADERS_JSP = "/jsps/ApprovalHeaders.jsp";
052: public static final String APPROVAL_LINES_JSP = "/jsps/ApprovalLines.jsp";
053: public static final String RECEIPT_SUMMARY_JSP = "/jsps/ReceiptSummary.jsp";
054: public static final String RECEIPT_DETAILS_JSP = "/jsps/ReceiptDetails.jsp";
055: public static final String QUOTATION_SUMMARY_JSP = "/jsps/QuotationSummary.jsp";
056: public static final String QUOTATION_DETAILS_JSP = "/jsps/QuotationDetails.jsp";
057: public static final String QUOTATION_LINES_JSP = "/jsps/QuotationLines.jsp";
058: public static final String NO_RECORDS_JSP = "/jsps/NoRecordsFound.jsp";
059: public static final String NO_DETAILS_JSP = "/jsps/NoDetailsFound.jsp";
060: public static final String HELP_REQUISITIONS_JSP = "/jsps/help/RequisitionsHelp.jsp";
061: public static final String HELP_APPROVAL_JSP = "/jsps/help/ApprovalHelp.jsp";
062: public static final String HELP_RECEIPTS_JSP = "/jsps/help/ReceiptsHelp.jsp";
063: public static final String HELP_QUOTATIONS_JSP = "/jsps/help/QuotationsHelp.jsp";
064:
065: /* Oracle Queries used by various Oracle Apps portlets */
066:
067: /* Oracle Queries to retrieve requisition information and details */
068:
069: public static final String REQUISITION_INFO_QUERY = "SELECT * "
070: + "FROM (SELECT " + "PRHA.REQUISITION_HEADER_ID, "
071: + "PRHA.SEGMENT1, " + "PRHA.DESCRIPTION, "
072: + "PRHA.TYPE_LOOKUP_CODE, " + "PRHA.CREATION_DATE, "
073: + "PAPF.FULL_NAME, " + "PRHA.AUTHORIZATION_STATUS "
074: + "FROM " + "PO_REQUISITION_HEADERS_ALL PRHA, "
075: + "PER_ALL_PEOPLE_F PAPF " + "WHERE "
076: + "PAPF.PERSON_ID=PRHA.PREPARER_ID "
077: + "AND PRHA.DESCRIPTION<>'null' "
078: + "ORDER BY PRHA.CREATION_DATE DESC) PRH2 "
079: + "WHERE rownum <= ";
080:
081: public static final String REQUISITION_DETAILS_QUERY = "SELECT "
082: + "PRLA.REQUISITION_LINE_ID, " + "PRLA.ITEM_DESCRIPTION, "
083: + "PRLA.SUGGESTED_VENDOR_NAME, " + "PRLA.UNIT_PRICE, "
084: + "PRLA.CURRENCY_CODE, " + "PRLA.QUANTITY, "
085: + "PRLA.NEED_BY_DATE, " + "HRL1.LOCATION_CODE " + "FROM "
086: + "PO_REQUISITION_LINES_ALL PRLA, "
087: + "HR_LOCATIONS_ALL HRL1 " + "WHERE "
088: + "HRL1.LOCATION_ID=PRLA.DELIVER_TO_LOCATION_ID "
089: + "AND PRLA.REQUISITION_HEADER_ID=";
090:
091: /* Queries to retrieve requisitions with pending approval and corresponding details */
092:
093: public static final String PENDING_APPROVAL_HEADERS_QUERY = "SELECT "
094: + "PRHA.SEGMENT1, "
095: + "PRHA.DESCRIPTION, "
096: + "PRHA.TYPE_LOOKUP_CODE, "
097: + "PRHA.CREATION_DATE, "
098: + "PAPF.FULL_NAME "
099: + "FROM "
100: + "PO_REQUISITION_HEADERS_ALL PRHA, "
101: + "PER_ALL_PEOPLE_F PAPF "
102: + "WHERE "
103: + "PRHA.AUTHORIZATION_STATUS='PRE-APPROVED' "
104: + "AND PAPF.PERSON_ID=PRHA.PREPARER_ID "
105: + "ORDER BY PRHA.CREATION_DATE DESC";
106:
107: public static final String APPROVAL_LINES_QUERY = "SELECT "
108: + "PRLA.REQUISITION_LINE_ID, " + "PRLA.ITEM_DESCRIPTION, "
109: + "PRLA.SUGGESTED_VENDOR_NAME, " + "PRLA.UNIT_PRICE, "
110: + "PRLA.CURRENCY_CODE, " + "PRLA.QUANTITY, "
111: + "PRLA.NEED_BY_DATE " + "FROM "
112: + "PO_REQUISITION_LINES_ALL PRLA " + "WHERE "
113: + "PRLA.REQUISITION_HEADER_ID=";
114:
115: public static final String APPROVAL_LINES_SUB_QUERY1 = "(SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1=";
116:
117: public static final String APPROVAL_LINES_SUB_QUERY2 = " AND AUTHORIZATION_STATUS='PRE-APPROVED')";
118:
119: /* Queries to retrieve receipt summary information and corresponding details */
120:
121: public static final String RECEIPT_SUMMARY_QUERY = "SELECT * "
122: + "FROM "
123: + "(SELECT "
124: + "ACR.RECEIPT_NUMBER, "
125: + "ACR.RECEIPT_DATE, "
126: + "ACR.AMOUNT, "
127: + "ACR.CURRENCY_CODE, "
128: + "ACR.STATUS "
129: + "FROM "
130: + "AR_CASH_RECEIPTS_ALL ACR "
131: + "ORDER BY ACR.RECEIPT_DATE DESC, ACR.RECEIPT_NUMBER DESC) ACR2 "
132: + "WHERE rownum <= ";
133:
134: public static final String RECEIPT_DETAILS_QUERY = "SELECT "
135: + "ACR.RECEIPT_NUMBER, " + "ACR.RECEIPT_DATE, "
136: + "ACR.AMOUNT, " + "ACR.CURRENCY_CODE, " + "ACR.TYPE, "
137: + "ARM.NAME, " + "HCA.ACCOUNT_NAME, "
138: + "ABA.BANK_ACCOUNT_NUM " + "FROM "
139: + "AR_CASH_RECEIPTS_ALL ACR, " + "AR_RECEIPT_METHODS ARM, "
140: + "HZ_CUST_ACCOUNTS HCA, " + "AP_BANK_ACCOUNTS_ALL ABA "
141: + "WHERE " + "ARM.RECEIPT_METHOD_ID=ACR.RECEIPT_METHOD_ID "
142: + "AND HCA.CUST_ACCOUNT_ID=ACR.PAY_FROM_CUSTOMER "
143: + "AND ABA.BANK_ACCOUNT_ID=ACR.REMITTANCE_BANK_ACCOUNT_ID ";
144:
145: public static final String RECEIPT_DETAILS_QC1 = "AND ACR.RECEIPT_NUMBER='";
146:
147: public static final String RECEIPT_DETAILS_QC2 = "' AND ACR.AMOUNT=";
148:
149: /* Queries to retrieve quotation summary information and corresponding details */
150:
151: public static final String QUOTATION_SUMMARY_QUERY = "SELECT * "
152: + "FROM " + "(SELECT " + "POH.SEGMENT1, "
153: + "POH.COMMENTS, " + "POV.VENDOR_NAME, "
154: + "POH.QUOTE_VENDOR_QUOTE_NUMBER, " + "PAPF.FULL_NAME, "
155: + "PDT.TYPE_NAME, " + "POH.STATUS_LOOKUP_CODE " + "FROM "
156: + "PO_HEADERS_ALL POH, " + "PO_VENDORS POV, "
157: + "PER_ALL_PEOPLE_F PAPF, " + "PO_DOCUMENT_TYPES_ALL PDT "
158: + "WHERE " + "POH.TYPE_LOOKUP_CODE='QUOTATION' "
159: + "AND POV.VENDOR_ID=POH.VENDOR_ID "
160: + "AND PAPF.PERSON_ID = POH.AGENT_ID "
161: + "AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE "
162: + "AND PAPF.EFFECTIVE_END_DATE "
163: + "AND PDT.ORG_ID=POH.ORG_ID "
164: + "AND PDT.DOCUMENT_TYPE_CODE=POH.TYPE_LOOKUP_CODE "
165: + "AND PDT.DOCUMENT_SUBTYPE=POH.QUOTE_TYPE_LOOKUP_CODE "
166: + "ORDER BY POH.SEGMENT1 DESC) POH2 " + "WHERE rownum <= ";
167:
168: public static final String QUOTATION_DETAILS_QUERY = "SELECT "
169: + "POH.SEGMENT1, " + "POV.VENDOR_NAME, "
170: + "POH.QUOTE_VENDOR_QUOTE_NUMBER, "
171: + "PVS.VENDOR_SITE_CODE, "
172: + "DECODE(POH.VENDOR_CONTACT_ID " + ", NULL " + ", NULL "
173: + ", PVC.LAST_NAME || ' " + ", ' || PVC.FIRST_NAME), "
174: + "HLA.LOCATION_CODE, " + "POH.START_DATE, "
175: + "POH.END_DATE " + "FROM " + "PO_HEADERS_ALL POH, "
176: + "PO_VENDORS POV, " + "PO_VENDOR_SITES_ALL PVS, "
177: + "PO_VENDOR_CONTACTS PVC, " + "HR_LOCATIONS_ALL HLA "
178: + "WHERE " + "POH.SEGMENT1='";
179:
180: public static final String QUOTATION_DETAILS_QC1 = "' AND POH.TYPE_LOOKUP_CODE='QUOTATION' "
181: + "AND POV.VENDOR_ID=POH.VENDOR_ID "
182: + "AND PVS.VENDOR_SITE_ID=POH.VENDOR_SITE_ID "
183: + "AND PVC.VENDOR_CONTACT_ID=POH.VENDOR_CONTACT_ID "
184: + "AND HLA.LOCATION_ID=POH.BILL_TO_LOCATION_ID";
185:
186: public static final String QUOTATION_LINES_QUERY = "SELECT "
187: + "PLA.LINE_NUM, "
188: + "PLA.ITEM_DESCRIPTION, "
189: + "MSI.SEGMENT1, "
190: + "MCA.DESCRIPTION, "
191: + "PLA.UNIT_MEAS_LOOKUP_CODE, "
192: + "PLA.UNIT_PRICE "
193: + "FROM "
194: + "PO_LINES_ALL PLA, "
195: + "MTL_SYSTEM_ITEMS MSI, "
196: + "MTL_CATEGORIES MCA "
197: + "WHERE "
198: + "MCA.CATEGORY_ID=PLA.CATEGORY_ID "
199: + "AND MSI.ORGANIZATION_ID=PLA.ORG_ID "
200: + "AND MSI.INVENTORY_ITEM_ID=PLA.ITEM_ID "
201: + "AND PLA.PO_HEADER_ID=(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1='";
202:
203: public static final String QUOTATION_LINES_QC1 = "' AND TYPE_LOOKUP_CODE='QUOTATION')";
204:
205: /* Parameter names related to the Oracle Apps Server, which are defined in SSO Adapter template */
206:
207: public static final String ORACLE_HOST_NAME = "OracleHostName";
208: public static final String ORACLE_PORT_NUM = "OracleHostPort";
209: public static final String ORACLE_SID = "OracleSID";
210: public static final String ORACLE_USER_ID = "OracleUserID";
211: public static final String ORACLE_USER_PWD = "OracleUserPassword";
212:
213: /* Parameter names related to the SSO Adapter Configuration */
214:
215: public static final String SSO_ORACLE_CONFIG_PREF_NAME = "oracle.ssoa.configname";
216: public static final String HTTPREQ_ATTR_NAME = "javax.portlet.portletc.httpServletRequest";
217:
218: /* Miscellaneous parameter names */
219: public static final String PAGINATION_ROWS_NUMBER = "NumberOfPaginationRows";
220: public static final String NUMBER_OF_REQUISITION_RECORDS = "NumberOfRequisitionRecords";
221: public static final String NUMBER_OF_RECEIPT_RECORDS = "NumberOfReceiptRecords";
222: public static final String NUMBER_OF_QUOTATION_RECORDS = "NumberOfQuotationRecords";
223:
224: }
|