[Home] [Help]
PACKAGE BODY: APPS.GMF_AR_GET_SREP_TERRITORIES
Source
1 PACKAGE BODY GMF_AR_GET_SREP_TERRITORIES as
2 /* $Header: gmfsaltb.pls 115.1 2002/11/11 00:41:48 rseshadr ship $ */
3 cursor cur_ar_get_srep_territories(start_date date,
4 end_date date,
5 salesrepid number,
6 territoryid number) is
7 select SRT.SALESREP_ID, SRT.TERRITORY_ID,
8 SRT.START_DATE_ACTIVE, SRT.END_DATE_ACTIVE,
9 SRT.STATUS, TER.NAME,
10 TER.SEGMENT1, TER.SEGMENT2,
11 TER.SEGMENT3, TER.SEGMENT4,
12 TER.SEGMENT5, TER.SEGMENT6,
13 TER.SEGMENT7, TER.SEGMENT8,
14 TER.SEGMENT9, TER.SEGMENT10,
15 TER.SEGMENT11, TER.SEGMENT12,
16 TER.SEGMENT13, TER.SEGMENT14,
17 TER.SEGMENT15, TER.SEGMENT16,
18 TER.SEGMENT17, TER.SEGMENT18,
19 TER.SEGMENT19, TER.SEGMENT20,
20 SRT.ATTRIBUTE_CATEGORY, SRT.ATTRIBUTE1,
21 SRT.ATTRIBUTE2, SRT.ATTRIBUTE3,
22 SRT.ATTRIBUTE4, SRT.ATTRIBUTE5,
23 SRT.ATTRIBUTE6, SRT.ATTRIBUTE7,
24 SRT.ATTRIBUTE8, SRT.ATTRIBUTE9,
25 SRT.ATTRIBUTE10, SRT.ATTRIBUTE11,
26 SRT.ATTRIBUTE12, SRT.ATTRIBUTE13,
27 SRT.ATTRIBUTE14, SRT.ATTRIBUTE15,
28 SRT.CREATED_BY, SRT.CREATION_DATE,
29 SRT.LAST_UPDATE_DATE, SRT.LAST_UPDATED_BY
30 from RA_SALESREP_TERRITORIES SRT,
31 RA_TERRITORIES TER
32 where SRT.salesrep_id = nvl(salesrepid, SRT.salesrep_id)
33 and SRT.territory_id = nvl(territoryid, SRT.territory_id)
34 and TER.territory_id = SRT.territory_id
35 and SRT.last_update_date between
36 nvl(start_date, SRT.last_update_date)
37 and nvl(end_date, SRT.last_update_date);
38
39 procedure AR_GET_SALESREP_TERRITORIES (salesrepid in out NOCOPY number,
40 territoryid in out NOCOPY number,
41 start_date in out NOCOPY date,
42 end_date in out NOCOPY date,
43 start_date_active out NOCOPY date,
44 end_date_active out NOCOPY date,
45 status out NOCOPY varchar2,
46 territory_name out NOCOPY varchar2,
47 segment1 out NOCOPY varchar2,
48 segment2 out NOCOPY varchar2,
49 segment3 out NOCOPY varchar2,
50 segment4 out NOCOPY varchar2,
51 segment5 out NOCOPY varchar2,
52 segment6 out NOCOPY varchar2,
53 segment7 out NOCOPY varchar2,
54 segment8 out NOCOPY varchar2,
55 segment9 out NOCOPY varchar2,
56 segment10 out NOCOPY varchar2,
57 segment11 out NOCOPY varchar2,
58 segment12 out NOCOPY varchar2,
59 segment13 out NOCOPY varchar2,
60 segment14 out NOCOPY varchar2,
61 segment15 out NOCOPY varchar2,
62 segment16 out NOCOPY varchar2,
63 segment17 out NOCOPY varchar2,
64 segment18 out NOCOPY varchar2,
65 segment19 out NOCOPY varchar2,
66 segment20 out NOCOPY varchar2,
67 attr_category out NOCOPY varchar2,
68 att1 out NOCOPY varchar2,
69 att2 out NOCOPY varchar2,
70 att3 out NOCOPY varchar2,
71 att4 out NOCOPY varchar2,
72 att5 out NOCOPY varchar2,
73 att6 out NOCOPY varchar2,
74 att7 out NOCOPY varchar2,
75 att8 out NOCOPY varchar2,
76 att9 out NOCOPY varchar2,
77 att10 out NOCOPY varchar2,
78 att11 out NOCOPY varchar2,
79 att12 out NOCOPY varchar2,
80 att13 out NOCOPY varchar2,
81 att14 out NOCOPY varchar2,
82 att15 out NOCOPY varchar2,
83 created_by out NOCOPY varchar2,
84 creation_date out NOCOPY date,
85 last_update_date out NOCOPY date,
86 last_updated_by out NOCOPY varchar2,
87 row_to_fetch in out NOCOPY number,
88 error_status out NOCOPY number) is
89
90 createdby number;
91 modifiedby number;
92
93 begin
94
95 if NOT cur_ar_get_srep_territories%ISOPEN then
96 open cur_ar_get_srep_territories(start_date, end_date,
97 salesrepid, territoryid);
98 end if;
99
100 fetch cur_ar_get_srep_territories
101 into salesrepid, territoryid, start_date_active,
102 end_date_active, status, territory_name,
103 segment1, segment2, segment3,
104 segment4, segment5, segment6,
105 segment7, segment8, segment9,
106 segment10, segment11, segment12,
107 segment13, segment14, segment15,
108 segment16, segment17, segment18,
109 segment19, segment20, attr_category,
110 att1, att2, att3,
111 att4, att5, att6,
112 att7, att8, att9,
113 att10, att11, att12,
114 att13, att14, att15,
115 createdby, creation_date, last_update_date,
116 modifiedby;
117
118 if cur_ar_get_srep_territories%NOTFOUND then
119 error_status := 100;
120 close cur_ar_get_srep_territories;
121 else
122 created_by := gmf_fnd_get_users.fnd_get_users(createdby);
123 last_updated_by := gmf_fnd_get_users.fnd_get_users(modifiedby);
124 end if;
125 if row_to_fetch = 1 and cur_ar_get_srep_territories%ISOPEN then
126 close cur_ar_get_srep_territories;
127 end if;
128
129 exception
130
131 when others then
132 error_status := SQLCODE;
133
134 end AR_GET_SALESREP_TERRITORIES;
135 END GMF_AR_GET_SREP_TERRITORIES;