1 PACKAGE BODY GMI_CMXFER01_XMLP_PKG AS
2 /* $Header: CMXFER01B.pls 120.0 2007/12/24 13:12:22 nchinnam noship $ */
3 FUNCTION RELEASEDATECFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 IF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NOT NULL THEN
6 RELEASEDATECP := ' and xf.actual_release_date >= to_date(''' || TO_CHAR(P_FROM_RELEASE_DATE
7 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ' || ' and xf.actual_release_date < to_date(''' || TO_CHAR(P_TO_RELEASE_DATE
8 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
9 ELSIF P_FROM_RELEASE_DATE IS NULL AND P_TO_RELEASE_DATE IS NULL THEN
10 RELEASEDATECP := ' ';
11 ELSIF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NULL THEN
12 RELEASEDATECP := ' and xf.actual_release_date >= to_date(''' || TO_CHAR(P_FROM_RELEASE_DATE
13 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ';
14 ELSIF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NOT NULL THEN
15 RELEASEDATECP := ' and xf.actual_release_date < to_date(''' || TO_CHAR(P_TO_RELEASE_DATE
16 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
17 END IF;
18 RETURN RELEASEDATECP;
19 END RELEASEDATECFFORMULA;
20
21 FUNCTION SOURCEWAREHOUSECFFORMULA RETURN VARCHAR2 IS
22 BEGIN
23 IF LP_FROM_WAREHOUSE IS NOT NULL AND LP_TO_WAREHOUSE IS NOT NULL THEN
24 SOURCEWAREHOUSECP := ' and xf.from_warehouse >= ''' || LP_FROM_WAREHOUSE || '''
25 and xf.from_warehouse <= ''' || LP_TO_WAREHOUSE || '''';
26 ELSIF LP_FROM_WAREHOUSE IS NULL AND LP_TO_WAREHOUSE IS NULL THEN
27 SOURCEWAREHOUSECP := ' ';
28 LP_FROM_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
29 LP_TO_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
30 ELSIF LP_FROM_WAREHOUSE IS NOT NULL AND LP_TO_WAREHOUSE IS NULL THEN
31 SOURCEWAREHOUSECP := ' and xf.from_warehouse >= ''' || LP_FROM_WAREHOUSE || '''';
32 LP_TO_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
33 ELSIF LP_FROM_WAREHOUSE IS NOT NULL AND LP_TO_WAREHOUSE IS NOT NULL THEN
34 SOURCEWAREHOUSECP := ' and xf.from_warehouse <= ''' || LP_TO_WAREHOUSE || '''';
35 LP_FROM_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
36 END IF;
37 RETURN SOURCEWAREHOUSECP;
38 END SOURCEWAREHOUSECFFORMULA;
39
40 FUNCTION DESTINATIONWAREHOUSECFFORMULA RETURN VARCHAR2 IS
41 BEGIN
42 IF LP_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND LP_TO_DESTINATION_WAREHOUSE IS NOT NULL THEN
43 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse >= ''' || LP_FROM_DESTINATION_WAREHOUSE || '''
44 and xf.to_warehouse <= ''' || LP_TO_DESTINATION_WAREHOUSE || '''';
45 ELSIF LP_FROM_DESTINATION_WAREHOUSE IS NULL AND LP_TO_DESTINATION_WAREHOUSE IS NULL THEN
46 DESTINATIONWAREHOUSECP := ' ';
47 LP_FROM_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
48 LP_TO_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
49 ELSIF LP_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND LP_TO_DESTINATION_WAREHOUSE IS NULL THEN
50 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse >= ''' || LP_FROM_DESTINATION_WAREHOUSE || '''';
51 LP_TO_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
52 ELSIF LP_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND LP_TO_DESTINATION_WAREHOUSE IS NOT NULL THEN
53 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse <= ''' || LP_TO_DESTINATION_WAREHOUSE || '''';
54 LP_FROM_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
55 END IF;
56 RETURN DESTINATIONWAREHOUSECP;
57 END DESTINATIONWAREHOUSECFFORMULA;
58
59 FUNCTION TREANSFERSTATUSCFFORMULA RETURN VARCHAR2 IS
60 BEGIN
61 IF LP_FROM_TRANSFER_STATUS IS NOT NULL AND LP_TO_TRANSFER_STATUS IS NOT NULL THEN
62 TRANSFERSTATUSCP := ' and ge.meaning >= ''' || LP_FROM_TRANSFER_STATUS || '''
63 and ge.meaning <= ''' || LP_TO_TRANSFER_STATUS || '''';
64 ELSIF LP_FROM_TRANSFER_STATUS IS NULL AND LP_TO_TRANSFER_STATUS IS NULL THEN
65 TRANSFERSTATUSCP := ' ';
66 LP_FROM_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
67 LP_TO_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
68 ELSIF LP_FROM_TRANSFER_STATUS IS NOT NULL AND LP_TO_TRANSFER_STATUS IS NULL THEN
69 TRANSFERSTATUSCP := ' and ge.meaning >= ''' || LP_FROM_TRANSFER_STATUS || '''';
70 LP_TO_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
71 ELSIF LP_FROM_TRANSFER_STATUS IS NOT NULL AND LP_TO_TRANSFER_STATUS IS NOT NULL THEN
72 TRANSFERSTATUSCP := ' and ge.meaning <= ''' || LP_TO_TRANSFER_STATUS || '''';
73 LP_FROM_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
74 END IF;
75 RETURN TRANSFERSTATUSCP;
76 END TREANSFERSTATUSCFFORMULA;
77
78 FUNCTION SCHEDULEDSHIPDATECFFORMULA RETURN VARCHAR2 IS
79 BEGIN
80 IF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NOT NULL THEN
81 SCHEDULEDSHIPDATECP := ' and xf.scheduled_release_date >= to_date( ''' || TO_CHAR(P_FROM_SCHEDULED_RELEASE_DATE
82 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ' || ' and xf.scheduled_release_date < to_date( ''' || TO_CHAR(P_TO_SCHEDULED_RELEASE_DATE
83 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
84 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NULL THEN
85 SCHEDULEDSHIPDATECP := ' ';
86 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NULL THEN
87 SCHEDULEDSHIPDATECP := ' and xf.scheduled_release_date >= to_date( ''' || TO_CHAR(P_FROM_SCHEDULED_RELEASE_DATE
88 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ';
89 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NOT NULL THEN
90 SCHEDULEDSHIPDATECP := ' and xf.scheduled_release_date < to_date( ''' || TO_CHAR(P_TO_SCHEDULED_RELEASE_DATE
91 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
92 END IF;
93 RETURN SCHEDULEDSHIPDATECP;
94 END SCHEDULEDSHIPDATECFFORMULA;
95
96 FUNCTION CANCELDATECFFORMULA RETURN VARCHAR2 IS
97 BEGIN
98 IF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NOT NULL THEN
99 CANCELDATECP := ' and xf.cancel_date >= to_date( ''' || TO_CHAR(P_FROM_CANCEL_DATE
100 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ' || ' and xf.cancel_date < to_date( ''' || TO_CHAR(P_TO_CANCEL_DATE
101 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
102 ELSIF P_FROM_CANCEL_DATE IS NULL AND P_TO_CANCEL_DATE IS NULL THEN
103 CANCELDATECP := ' ';
104 ELSIF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NULL THEN
105 CANCELDATECP := ' and xf.cancel_date >= to_date( ''' || TO_CHAR(P_FROM_CANCEL_DATE
106 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ';
107 ELSIF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NOT NULL THEN
108 CANCELDATECP := ' and xf.cancel_date < to_date( ''' || TO_CHAR(P_TO_CANCEL_DATE
109 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
110 END IF;
111 RETURN CANCELDATECP;
112 END CANCELDATECFFORMULA;
113
114 FUNCTION ACTUALRECEIVEDATECFFORMULA RETURN VARCHAR2 IS
115 BEGIN
116 IF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NOT NULL THEN
117 ACTUALRECEIVEDATECP := ' and xf.actual_receive_date >= to_date( ''' || TO_CHAR(P_FROM_ACTUAL_RECEIVE_DATE
118 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ' || ' and xf.actual_receive_date < to_date( ''' || TO_CHAR(P_TO_ACTUAL_RECEIVE_DATE
119 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
120 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NULL THEN
121 ACTUALRECEIVEDATECP := ' ';
122 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NULL THEN
123 ACTUALRECEIVEDATECP := ' and xf.actual_receive_date >= to_date( ''' || TO_CHAR(P_FROM_ACTUAL_RECEIVE_DATE
124 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) ';
125 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NOT NULL THEN
126 ACTUALRECEIVEDATECP := ' and xf.actual_receive_date < to_date( ''' || TO_CHAR(P_TO_ACTUAL_RECEIVE_DATE
127 ,'YYYY/MM/DD HH24:MI:SS') || ''', ''YYYY/MM/DD HH24:MI:SS'' ) + 1 ';
128 END IF;
129 RETURN ACTUALRECEIVEDATECP;
130 END ACTUALRECEIVEDATECFFORMULA;
131
132 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
133 BEGIN
134 LP_FROM_RELEASE_DATE:=to_char(P_FROM_RELEASE_DATE,'DD-MON-YY');
135 LP_TO_RELEASE_DATE:=to_char(P_TO_RELEASE_DATE,'DD-MON-YY');
136 LP_FROM_SCHEDULED_RELEASE_DATE:=to_char(P_FROM_SCHEDULED_RELEASE_DATE,'DD-MON-YY');
137 LP_TO_SCHEDULED_RELEASE_DATE:=to_char(P_TO_SCHEDULED_RELEASE_DATE,'DD-MON-YY');
138 LP_FROM_ACTUAL_RECEIVE_DATE:=to_char(P_FROM_ACTUAL_RECEIVE_DATE,'DD-MON-YY');
139 LP_TO_ACTUAL_RECEIVE_DATE:=to_char(P_TO_ACTUAL_RECEIVE_DATE,'DD-MON-YY');
140 LP_FROM_CANCEL_DATE:=to_char(P_FROM_CANCEL_DATE,'DD-MON-YY');
141 LP_TO_CANCEL_DATE:=to_char(P_TO_CANCEL_DATE,'DD-MON-YY');
142 LP_FROM_WAREHOUSE:=P_FROM_WAREHOUSE;
143 LP_TO_WAREHOUSE:=P_TO_WAREHOUSE;
144 LP_FROM_DESTINATION_WAREHOUSE:=P_FROM_DESTINATION_WAREHOUSE;
145 LP_TO_DESTINATION_WAREHOUSE:=P_TO_DESTINATION_WAREHOUSE;
146 LP_FROM_TRANSFER_STATUS:=P_FROM_TRANSFER_STATUS;
147 LP_TO_TRANSFER_STATUS:=P_TO_TRANSFER_STATUS;
148 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
149 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
150 RETURN (TRUE);
151 END BEFOREREPORT;
152
153 PROCEDURE HEADER IS
154 BEGIN
155 NULL;
156 END HEADER;
157
158 FUNCTION AFTERREPORT RETURN BOOLEAN IS
159 BEGIN
160 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
161 RETURN (TRUE);
162 END AFTERREPORT;
163
164 FUNCTION RELEASEDATECP_P RETURN VARCHAR2 IS
165 BEGIN
166 RETURN RELEASEDATECP;
167 END RELEASEDATECP_P;
168
169 FUNCTION SOURCEWAREHOUSECP_P RETURN VARCHAR2 IS
170 BEGIN
171 RETURN SOURCEWAREHOUSECP;
172 END SOURCEWAREHOUSECP_P;
173
174 FUNCTION DESTINATIONWAREHOUSECP_P RETURN VARCHAR2 IS
175 BEGIN
176 RETURN DESTINATIONWAREHOUSECP;
177 END DESTINATIONWAREHOUSECP_P;
178
179 FUNCTION TRANSFERSTATUSCP_P RETURN VARCHAR2 IS
180 BEGIN
181 RETURN TRANSFERSTATUSCP;
182 END TRANSFERSTATUSCP_P;
183
184 FUNCTION SCHEDULEDSHIPDATECP_P RETURN VARCHAR2 IS
185 BEGIN
186 RETURN SCHEDULEDSHIPDATECP;
187 END SCHEDULEDSHIPDATECP_P;
188
189 FUNCTION CANCELDATECP_P RETURN VARCHAR2 IS
190 BEGIN
191 RETURN CANCELDATECP;
192 END CANCELDATECP_P;
193
194 FUNCTION ACTUALRECEIVEDATECP_P RETURN VARCHAR2 IS
195 BEGIN
196 RETURN ACTUALRECEIVEDATECP;
197 END ACTUALRECEIVEDATECP_P;
198
199 END GMI_CMXFER01_XMLP_PKG;
200
[Home] [Help]