1 PACKAGE BODY INV_INVINPSR_XMLP_PKG AS
2 /* $Header: INVINPSRB.pls 120.5 2011/02/11 14:48:19 abhissri ship $ */
3 FUNCTION INS_PKSLIP_REC(OLD_PACKSLIP_NUMBER IN NUMBER
4 ,LINE_NO IN NUMBER
5 ,C_PACKSLIP_NUM IN NUMBER
6 ,SHIP_HEADER_ID IN NUMBER) RETURN VARCHAR2 IS
7 BEGIN
8 /*SRW.REFERENCE(OLD_PACKSLIP_NUMBER)*/NULL;
9 IF OLD_PACKSLIP_NUMBER = 0 THEN
10 IF LINE_NO = 1 THEN
11 /*INSERT INTO MTL_INV_PACKSLIPS
12 (PACKSLIP_NUMBER
13 ,PACKSLIP_DATE
14 ,SHIPMENT_HEADER_ID)
15 VALUES (C_PACKSLIP_NUM
16 ,SYSDATE
17 ,SHIP_HEADER_ID);*/null;
18 END IF;
19 END IF;
20 RETURN ('YES');
21 END INS_PKSLIP_REC;
22
23 FUNCTION PSLIP_NUMBER(OLD_PACKSLIP_NUMBER IN NUMBER
24 ,LINE_NO IN NUMBER) RETURN NUMBER IS
25 BEGIN
26 DECLARE
27 PACKSLIP_NUM NUMBER;
28 BEGIN
29 IF (OLD_PACKSLIP_NUMBER = 0 AND LINE_NO = 1) THEN
30 SELECT
31 MTL_INV_PACKSLIPS_S.NEXTVAL
32 INTO PACKSLIP_NUM
33 FROM
34 SYS.DUAL;
35 ELSE
36 PACKSLIP_NUM := OLD_PACKSLIP_NUMBER;
37 END IF;
38 RETURN (PACKSLIP_NUM);
39 END;
40 END PSLIP_NUMBER;
41
42 FUNCTION AFTERREPORT RETURN BOOLEAN IS
43 BEGIN
44 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
45 RETURN (TRUE);
46 END AFTERREPORT;
47
48 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
49 BEGIN
50 DECLARE
51 SHIP_NUM NUMBER;
52 BEGIN
53 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
54 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
55 SHIP_NUM := -1;
56 FND_MESSAGE.SET_NAME('INV'
57 ,'INV_NO_DATA_EXISTS');
58 C_MESSAGE_NO_DATA := SUBSTR(FND_MESSAGE.GET
59 ,1
60 ,50);
61 C_MESSAGE_NO_DATA := '*** ' || C_MESSAGE_NO_DATA || ' ***';
62 EXCEPTION
63 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
64 BEGIN
65 /*SRW.MESSAGE(100
66 ,'Foundation is not initialised')*/NULL;
67 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
68 END;
69 END;
70 RETURN (TRUE);
71 END BEFOREREPORT;
72
73 FUNCTION C_SHIPNUM_WHERE RETURN VARCHAR2 IS
74 BEGIN
75 IF P_SHIPNO_LO IS NOT NULL AND P_SHIPNO_HIGH IS NOT NULL THEN
76 RETURN ('and h.shipment_num between ''' || P_SHIPNO_LO || '''
77 and ''' || P_SHIPNO_HIGH || ''' ');
78 ELSE
79 IF P_SHIPNO_LO IS NULL AND P_SHIPNO_HIGH IS NOT NULL THEN
80 RETURN ('and h.shipment_num = ''' || P_SHIPNO_HIGH || ''' ');
81 ELSE
82 IF P_SHIPNO_LO IS NOT NULL AND P_SHIPNO_HIGH IS NULL THEN
83 RETURN ('and h.shipment_num = ''' || P_SHIPNO_LO || ''' ');
84 ELSE
85 RETURN (' ');
86 END IF;
87 END IF;
88 END IF;
89 RETURN ' ';
90 END C_SHIPNUM_WHERE;
91
92 FUNCTION C_PACKSLIP_WHERE RETURN VARCHAR2 IS
93 BEGIN
94 IF P_PACKSLIP_LO IS NOT NULL AND P_PACKSLIP_HIGH IS NOT NULL THEN
95 RETURN ('and mip.packslip_number between to_char(''' || P_PACKSLIP_LO || ''')
96 and to_char(''' || P_PACKSLIP_HIGH || ''') ');
97 ELSE
98 IF P_PACKSLIP_LO IS NULL AND P_PACKSLIP_HIGH IS NOT NULL THEN
99 RETURN ('and mip.packslip_number = to_char(''' || P_PACKSLIP_HIGH || ''') ');
100 ELSE
101 IF P_PACKSLIP_LO IS NOT NULL AND P_PACKSLIP_HIGH IS NULL THEN
102 RETURN ('and mip.packslip_number = to_char(''' || P_PACKSLIP_LO || ''') ');
103 ELSE
104 RETURN ('and nvl(mip.packslip_number,0) = 0');
105 RETURN (' ');
106 END IF;
107 END IF;
108 END IF;
109 RETURN NULL;
110 END C_PACKSLIP_WHERE;
111
112 FUNCTION C_SHIP_VIA_WHERE RETURN VARCHAR2 IS
113 BEGIN
114 IF P_SHIP_VIA_LO IS NOT NULL AND P_SHIP_VIA_HIGH IS NOT NULL THEN
115 RETURN ('and orgf.freight_code between ''' || P_SHIP_VIA_LO || '''
116 and ''' || P_SHIP_VIA_HIGH || ''' ');
117 ELSE
118 IF P_SHIP_VIA_LO IS NULL AND P_SHIP_VIA_HIGH IS NOT NULL THEN
119 RETURN ('and orgf.freight_code = ''' || P_SHIP_VIA_HIGH || ''' ');
120 ELSE
121 IF P_SHIP_VIA_LO IS NOT NULL AND P_SHIP_VIA_HIGH IS NULL THEN
122 RETURN ('and orgf.freight_code = ''' || P_SHIP_VIA_LO || ''' ');
123 ELSE
124 RETURN (' ');
125 END IF;
126 END IF;
127 END IF;
128 RETURN NULL;
129 END C_SHIP_VIA_WHERE;
130
131 FUNCTION C_REC_ORG_WHERE RETURN VARCHAR2 IS
132 BEGIN
133 IF P_RECEIVING_ORG_LO IS NOT NULL AND P_RECEIVING_ORG_HIGH IS NOT NULL THEN
134 RETURN ('and l.to_organization_id between ''' || TO_CHAR(P_RECEIVING_ORG_LO) || ''' and
135 ''' || TO_CHAR(P_RECEIVING_ORG_HIGH) || ''' ');
136 ELSE
137 IF P_RECEIVING_ORG_LO IS NULL AND P_RECEIVING_ORG_HIGH IS NOT NULL THEN
138 RETURN ('and l.to_organization_id = ''' || TO_CHAR(P_RECEIVING_ORG_HIGH) || ''' ');
139 ELSE
140 IF P_RECEIVING_ORG_LO IS NOT NULL AND P_RECEIVING_ORG_HIGH IS NULL THEN
141 RETURN ('and l.to_organization_id = ''' || TO_CHAR(P_RECEIVING_ORG_LO) || ''' ');
142 ELSE
143 RETURN (' ');
144 END IF;
145 END IF;
146 END IF;
147 RETURN ' ';
148 END C_REC_ORG_WHERE;
149
150 FUNCTION C_TRANS_TYPE_WHERE RETURN VARCHAR2 IS
151 BEGIN
152 IF P_TRANS_TYPE_ID_LO IS NOT NULL AND P_TRANS_TYPE_ID_HIGH IS NOT NULL THEN
153 RETURN ('and mtt.transaction_type_id between ' || P_TRANS_TYPE_ID_LO || '
154 and ' || P_TRANS_TYPE_ID_HIGH);
155 ELSE
156 IF P_TRANS_TYPE_ID_LO IS NULL AND P_TRANS_TYPE_ID_HIGH IS NOT NULL THEN
157 RETURN ('and mtt.transaction_type_id = ' || P_TRANS_TYPE_ID_HIGH);
158 ELSE
159 IF P_TRANS_TYPE_ID_LO IS NOT NULL AND P_TRANS_TYPE_ID_HIGH IS NULL THEN
160 RETURN ('and mtt.transaction_type_id = ' || P_TRANS_TYPE_ID_LO);
161 ELSE
162 RETURN (' ');
163 END IF;
164 END IF;
165 END IF;
166 RETURN ' ';
167 END C_TRANS_TYPE_WHERE;
168
169 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
170 CURSOR C_SOB IS
171 SELECT
172 SET_OF_BOOKS_ID
173 FROM
174 AP_SYSTEM_PARAMETERS;
175 BEGIN
176 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
177 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
178 IF (P_SET_OF_BOOKS_ID IS NULL) THEN
179 OPEN C_SOB;
180 FETCH C_SOB
181 INTO P_SET_OF_BOOKS_ID;
182 CLOSE C_SOB;
183 END IF;
184 RETURN (TRUE);
185 EXCEPTION
186 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
187 BEGIN
188 /*SRW.MESSAGE(100
189 ,'Foundation is not initialised')*/NULL;
190 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
191 END;
192 RETURN NULL;
193 END BEFOREPFORM;
194 --#11703252 Character date international calendar support
195 FUNCTION C_SHIPPED_DATEFORMULA(SHIPPED_DATE IN DATE) RETURN CHAR IS
196 BEGIN
197 RETURN FND_DATE.DATE_TO_CHARDATE(SHIPPED_DATE,calendar_aware=>FND_DATE.calendar_aware_alt);
198 END C_SHIPPED_DATEFORMULA;
199
200 FUNCTION CF_BLANKFORMULA RETURN CHAR IS
201 BEGIN
202 RETURN (' ');
203 END CF_BLANKFORMULA;
204
205 FUNCTION CF_TAX_REGISTRATION_NUMBERFORM RETURN VARCHAR2 IS
206 L_DEF_TAX_REG_NO VARCHAR2(50);
207 BEGIN
208 BEGIN
209 SELECT
210 ZR.REGISTRATION_NUMBER
211 INTO L_DEF_TAX_REG_NO
212 FROM
213 ZX_PARTY_TAX_PROFILE ZPTP,
214 ZX_REGISTRATIONS ZR,
215 XLE_ASSOCIATIONS_V XAV
216 WHERE ZPTP.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID
217 AND ZR.DEFAULT_REGISTRATION_FLAG = 'Y'
218 AND ZPTP.PARTY_ID = XAV.LEGAL_PARTY_ID
219 AND ZPTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
220 AND XAV.ENTITY_ID = P_ISSUING_ORG
221 AND XAV.ENTITY_TYPE = 'INVENTORY_ORGANIZATION';
222 RETURN L_DEF_TAX_REG_NO;
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 SELECT
226 ZR.REGISTRATION_NUMBER
227 INTO L_DEF_TAX_REG_NO
228 FROM
229 XLE_ETB_PROFILES XEP,
230 ZX_PARTY_TAX_PROFILE ZPTP,
231 ZX_REGISTRATIONS ZR,
232 ORG_ORGANIZATION_DEFINITIONS OOD
233 WHERE ZPTP.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID
234 AND ZR.DEFAULT_REGISTRATION_FLAG = 'Y'
235 AND ZPTP.PARTY_ID = XEP.PARTY_ID
236 AND ZPTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
237 AND XEP.LEGAL_ENTITY_ID = OOD.LEGAL_ENTITY
238 AND XEP.MAIN_ESTABLISHMENT_FLAG = 'Y'
239 AND OOD.ORGANIZATION_ID = P_ISSUING_ORG;
240 RETURN L_DEF_TAX_REG_NO;
241 END;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 RETURN (NULL);
245 END CF_TAX_REGISTRATION_NUMBERFORM;
246
247 FUNCTION C_MESSAGE_NO_DATA_P RETURN VARCHAR2 IS
248 BEGIN
249 RETURN C_MESSAGE_NO_DATA;
250 END C_MESSAGE_NO_DATA_P;
251
252 END INV_INVINPSR_XMLP_PKG;
253
[Home] [Help]