[Home] [Help]
PACKAGE BODY: APPS.GMF_AR_GET_SALESREPS
Source
1 PACKAGE BODY GMF_AR_GET_SALESREPS AS
2 /* $Header: gmfrepdb.pls 115.8 2002/11/11 00:41:06 rseshadr ship $ */
3 CURSOR CUR_AR_GET_SALESREPS(START_DATE DATE,
4 END_DATE DATE,
5 SALESREP_NAME VARCHAR2,
6 SALESREPID NUMBER,
7 PORG_ID NUMBER) IS
8 SELECT substrb(SAR.NAME,1,40), SAR.SALESREP_ID,
9 SAR.SALES_CREDIT_TYPE_ID, nvl(SAR.STATUS,'A'),
10 SAR.START_DATE_ACTIVE, SAR.END_DATE_ACTIVE,
11 SAR.GL_ID_REV, SAR.GL_ID_FREIGHT,
12 SAR.GL_ID_REC, SAR.SET_OF_BOOKS_ID,
13 SAR.SALESREP_NUMBER, SAR.ATTRIBUTE_CATEGORY,
14 SAR.ATTRIBUTE1, SAR.ATTRIBUTE2,
15 SAR.ATTRIBUTE3, SAR.ATTRIBUTE4,
16 SAR.ATTRIBUTE5, SAR.ATTRIBUTE6,
17 SAR.ATTRIBUTE7, SAR.ATTRIBUTE8,
18 SAR.ATTRIBUTE9, SAR.ATTRIBUTE10,
19 SAR.ATTRIBUTE11, SAR.ATTRIBUTE12,
20 SAR.ATTRIBUTE13, SAR.ATTRIBUTE14,
21 SAR.ATTRIBUTE15, SAR.CREATED_BY,
22 SAR.CREATION_DATE, SAR.LAST_UPDATE_DATE,
23 SAR.LAST_UPDATED_BY, SAR.ORG_ID
24 FROM RA_SALESREPS SAR
25 WHERE LOWER(SAR.NAME) LIKE LOWER(NVL(SALESREP_NAME, SAR.NAME))
26 AND SAR.SALESREP_ID = NVL(SALESREPID, SAR.SALESREP_ID)
27 AND SAR.SALESREP_ID > 0
28 AND SAR.SALESREP_NUMBER is not NULL
29 AND SAR.NAME is not NULL
30 AND SAR.LAST_UPDATE_DATE BETWEEN
31 NVL(START_DATE, SAR.LAST_UPDATE_DATE)
32 AND NVL(END_DATE, SAR.LAST_UPDATE_DATE)
33 AND NVL(SAR.ORG_ID,0) = NVL(PORG_ID, NVL(SAR.ORG_ID,0));
34 PROCEDURE AR_GET_SALESREPS (SALESREP_NAME IN OUT NOCOPY VARCHAR2,
35 SALESREPID IN OUT NOCOPY NUMBER,
36 START_DATE IN OUT NOCOPY DATE,
37 END_DATE IN OUT NOCOPY DATE,
38 SALES_CRE_TYPEID OUT NOCOPY NUMBER,
39 STATUS OUT NOCOPY VARCHAR2,
40 START_DATE_ACTIVE OUT NOCOPY DATE,
41 END_DATE_ACTIVE OUT NOCOPY DATE,
42 GL_ID_REV OUT NOCOPY NUMBER,
43 GL_ID_FREIGHT OUT NOCOPY NUMBER,
44 GL_ID_REC OUT NOCOPY NUMBER,
45 SOB_ID OUT NOCOPY NUMBER,
46 SALESREP_NUMBER IN OUT NOCOPY VARCHAR2,
47 ATTR_CATEGORY OUT NOCOPY VARCHAR2,
48 ATT1 OUT NOCOPY VARCHAR2,
49 ATT2 OUT NOCOPY VARCHAR2,
50 ATT3 OUT NOCOPY VARCHAR2,
51 ATT4 OUT NOCOPY VARCHAR2,
52 ATT5 OUT NOCOPY VARCHAR2,
53 ATT6 OUT NOCOPY VARCHAR2,
54 ATT7 OUT NOCOPY VARCHAR2,
55 ATT8 OUT NOCOPY VARCHAR2,
56 ATT9 OUT NOCOPY VARCHAR2,
57 ATT10 OUT NOCOPY VARCHAR2,
58 ATT11 OUT NOCOPY VARCHAR2,
59 ATT12 OUT NOCOPY VARCHAR2,
60 ATT13 OUT NOCOPY VARCHAR2,
61 ATT14 OUT NOCOPY VARCHAR2,
62 ATT15 OUT NOCOPY VARCHAR2,
63 CREATED_BY OUT NOCOPY NUMBER,
64 CREATION_DATE OUT NOCOPY DATE,
65 LAST_UPDATE_DATE OUT NOCOPY DATE,
66 LAST_UPDATED_BY OUT NOCOPY NUMBER,
67 ROW_TO_FETCH IN OUT NOCOPY NUMBER,
68 ERROR_STATUS OUT NOCOPY NUMBER,
69 PORG_ID IN OUT NOCOPY NUMBER) IS
70 /* CREATEDBY NUMBER; */
71 /* MODIFIEDBY NUMBER; */
72 BEGIN
73 IF NOT CUR_AR_GET_SALESREPS%ISOPEN THEN
74 OPEN CUR_AR_GET_SALESREPS(START_DATE,
75 END_DATE,
76 SALESREP_NAME,
77 SALESREPID,
78 PORG_ID);
79 END IF;
80 FETCH CUR_AR_GET_SALESREPS
81 INTO SALESREP_NAME, SALESREPID, SALES_CRE_TYPEID,
82 STATUS, START_DATE_ACTIVE, END_DATE_ACTIVE,
83 GL_ID_REV, GL_ID_FREIGHT, GL_ID_REC,
84 SOB_ID, SALESREP_NUMBER, ATTR_CATEGORY,
85 ATT1, ATT2, ATT3,
86 ATT4, ATT5, ATT6,
87 ATT7, ATT8, ATT9,
88 ATT10, ATT11, ATT12,
89 ATT13, ATT14, ATT15,
90 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
91 LAST_UPDATED_BY, PORG_ID;
92 IF CUR_AR_GET_SALESREPS%NOTFOUND THEN
93 ERROR_STATUS := 100;
94 CLOSE CUR_AR_GET_SALESREPS;
95 /* ELSE */
96 /* CREATED_BY := PKG_FND_GET_USERS.FND_GET_USERS(CREATEDBY); */
97 /* LAST_UPDATED_BY := PKG_FND_GET_USERS.FND_GET_USERS(MODIFIEDBY); */
98 END IF;
99 IF ROW_TO_FETCH = 1 AND CUR_AR_GET_SALESREPS%ISOPEN THEN
100 CLOSE CUR_AR_GET_SALESREPS;
101 END IF;
102 EXCEPTION
103 WHEN OTHERS THEN
104 ERROR_STATUS := SQLCODE;
105 END AR_GET_SALESREPS;
106 END GMF_AR_GET_SALESREPS;