1 PACKAGE BODY INV_INVPOPSR_XMLP_PKG AS
2 /* $Header: INVPOPSRB.pls 120.7 2011/02/11 14:39:24 abhissri ship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 RETURN (TRUE);
7 END AFTERREPORT;
8
9 FUNCTION C_ITEM_WHERE RETURN VARCHAR2 IS
10 BEGIN
11 IF P_PACKSLIP_LO IS NOT NULL OR P_PACKSLIP_HIGH IS NOT NULL THEN
12 RETURN (' ');
13 ELSE
14 IF P_ITEM_LO IS NOT NULL AND P_ITEM_HIGH IS NOT NULL THEN
15 RETURN ('and msi.description between ''' || P_ITEM_LO || ''' and
16 ''' || P_ITEM_HIGH || ''' ');
17 ELSE
18 IF P_ITEM_LO IS NULL AND P_ITEM_HIGH IS NOT NULL THEN
19 RETURN ('and msi.description = ''' || P_ITEM_HIGH || ''' ');
20 ELSE
21 IF P_ITEM_LO IS NOT NULL AND P_ITEM_HIGH IS NULL THEN
22 RETURN ('and msi.description = ''' || P_ITEM_LO || ''' ');
23 ELSE
24 RETURN (' ');
25 END IF;
26 END IF;
27 END IF;
28 END IF;
29 RETURN NULL;
30 END C_ITEM_WHERE;
31
32 FUNCTION C_PACKSLIP_WHERE RETURN VARCHAR2 IS
33 BEGIN
34 IF P_PACKSLIP_LO IS NOT NULL AND P_PACKSLIP_HIGH IS NOT NULL THEN
35 RETURN ('and mpp.packslip_number between to_number(''' || TO_CHAR(P_PACKSLIP_LO) || ''')
36 and to_number(''' || TO_CHAR(P_PACKSLIP_HIGH) || ''') ');
37 ELSE
38 IF P_PACKSLIP_LO IS NULL AND P_PACKSLIP_HIGH IS NOT NULL THEN
39 RETURN ('and mpp.packslip_number = to_number(''' || TO_CHAR(P_PACKSLIP_HIGH) || ''') ');
40 ELSE
41 IF P_PACKSLIP_LO IS NOT NULL AND P_PACKSLIP_HIGH IS NULL THEN
42 RETURN ('and mpp.packslip_number = to_number(''' || TO_CHAR(P_PACKSLIP_LO) || ''') ');
43 ELSE
44 RETURN ('and nvl(mpp.packslip_number,0) = 0');
45 END IF;
46 END IF;
47 END IF;
48 RETURN NULL;
49 END C_PACKSLIP_WHERE;
50
51 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
52 BEGIN
53 DECLARE
54 FUNCTCURR VARCHAR2(15);
55 ERRBUF VARCHAR2(132);
56 BEGIN
57 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
58 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
59 P_SHIP_DATE_LO := FND_DATE.CANONICAL_TO_DATE(P_SHIPMENT_DATE_LO);
60 P_SHIP_DATE_HIGH := FND_DATE.CANONICAL_TO_DATE(P_SHIPMENT_DATE_HIGH);
61 FND_MESSAGE.SET_NAME('INV'
62 ,'INV_NO_DATA_EXISTS');
63 C_MESSAGE_NO_DATA := SUBSTR(FND_MESSAGE.GET
64 ,1
65 ,50);
66 C_MESSAGE_NO_DATA := '*** ' || C_MESSAGE_NO_DATA || ' ***';
67 EXCEPTION
68 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
69 BEGIN
70 /*SRW.MESSAGE(100
71 ,'Foundation is not initialised')*/NULL;
72 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
73 END;
74 END;
75 RETURN (TRUE);
76 END BEFOREREPORT;
77
78 FUNCTION C_VENDOR_WHEREFORMULA RETURN VARCHAR2 IS
79 BEGIN
80 IF P_VENDOR_LO IS NOT NULL AND P_VENDOR_HIGH IS NOT NULL THEN
81 RETURN ('and v.vendor_name between ''' || P_VENDOR_LO || ''' and
82 ''' || P_VENDOR_HIGH || ''' ');
83 ELSE
84 IF P_VENDOR_LO IS NULL AND P_VENDOR_HIGH IS NOT NULL THEN
85 RETURN ('and v.vendor_name = ''' || P_VENDOR_HIGH || ''' ');
86 ELSE
87 IF P_VENDOR_LO IS NOT NULL AND P_VENDOR_HIGH IS NULL THEN
88 RETURN ('and v.vendor_name = ''' || P_VENDOR_LO || ''' ');
89 ELSE
90 RETURN (' ');
91 END IF;
92 END IF;
93 END IF;
94 RETURN ' ';
95 END C_VENDOR_WHEREFORMULA;
96
97 FUNCTION C_SHIPMENT_DATE_WHEREFORMULA RETURN VARCHAR2 IS
98 BEGIN
99 DECLARE
100 DUMMY VARCHAR2(150);
101 BEGIN
102 /*SRW.REFERENCE(P_SHIPMENT_DATE_LO)*/NULL;
103 /*SRW.REFERENCE(P_SHIPMENT_DATE_HIGH)*/NULL;
104 /*SRW.REFERENCE(P_SHIP_DATE_LO)*/NULL;
105 /*SRW.REFERENCE(P_SHIP_DATE_HIGH)*/NULL;
106 IF P_PACKSLIP_LO IS NOT NULL OR P_PACKSLIP_HIGH IS NOT NULL THEN
107 RETURN (' ');
108 ELSE
109 IF P_SHIPMENT_DATE_LO IS NOT NULL AND P_SHIPMENT_DATE_HIGH IS NOT NULL THEN
110 RETURN ('and trunc(trans.transaction_date) between to_date(''' || TO_CHAR(P_SHIP_DATE_LO
111 ,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') and to_date(''' || TO_CHAR(P_SHIP_DATE_HIGH
112 ,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') ');
113 ELSE
114 IF P_SHIPMENT_DATE_LO IS NULL AND P_SHIPMENT_DATE_HIGH IS NOT NULL THEN
115 RETURN ('and trunc(trans.transaction_date) = to_date(''' || TO_CHAR(P_SHIP_DATE_HIGH
116 ,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') ');
117 ELSE
118 IF P_SHIPMENT_DATE_LO IS NOT NULL AND P_SHIPMENT_DATE_HIGH IS NULL THEN
119 RETURN ('and trunc(trans.transaction_date) = to_date(''' || TO_CHAR(P_SHIP_DATE_LO
120 ,'YYYY/MM/DD') || ''' ,''YYYY/MM/DD'') ');
121 ELSE
122 RETURN (' ');
123 END IF;
124 END IF;
125 END IF;
126 END IF;
127 RETURN ' ';
128 END;
129 END C_SHIPMENT_DATE_WHEREFORMULA;
130
131 FUNCTION CF_DOCUMENT_NOFORMULA(OLD_PACKSLIP_NUMBER IN NUMBER
132 ,CS_VENDOR_COUNT IN NUMBER
133 ,CF_DOC_NUM IN NUMBER
134 ,TRANS_ID IN NUMBER) RETURN NUMBER IS
135 BEGIN
136 IF ((OLD_PACKSLIP_NUMBER = 0) AND (CS_VENDOR_COUNT = 1)) THEN
137 INSERT INTO MTL_PO_PACKSLIPS
138 (PACKSLIP_NUMBER
139 ,PACKSLIP_DATE
140 ,TRANSACTION_ID)
141 VALUES (CF_DOC_NUM
142 ,SYSDATE
143 ,TRANS_ID);
144 END IF;
145 RETURN (CF_DOC_NUM);
146 END CF_DOCUMENT_NOFORMULA;
147
148 FUNCTION CF_DOC_NUMFORMULA(OLD_PACKSLIP_NUMBER IN NUMBER
149 ,CS_VENDOR_COUNT IN NUMBER, VENDOR IN NUMBER) RETURN NUMBER IS
150 --L_NEW_PACKSLIP_NUM NUMBER;
151 BEGIN
152 IF ((OLD_PACKSLIP_NUMBER = 0) AND (CS_VENDOR_COUNT = 1)) THEN
153 IF (OLD_VENDOR IS NULL OR OLD_VENDOR <> VENDOR) THEN
154 SELECT
155 MTL_PO_PACKSLIPS_S.NEXTVAL
156 INTO L_NEW_PACKSLIP_NUM
157 FROM
158 DUAL;
159 OLD_VENDOR := VENDOR;
160 RETURN (L_NEW_PACKSLIP_NUM);
161 ELSE
162 RETURN (L_NEW_PACKSLIP_NUM);
163 END IF;
164 ELSE
165 RETURN (OLD_PACKSLIP_NUMBER);
166 END IF;
167 RETURN NULL;
168 END CF_DOC_NUMFORMULA;
169
170 FUNCTION G_TRANSACTIONGROUPFILTER(TRANS_ID IN NUMBER) RETURN BOOLEAN IS
171 BEGIN
172 /*SRW.MESSAGE('9999'
173 ,'Paddy - Trans ID = ' || TO_CHAR(TRANS_ID))*/NULL;
174 RETURN (TRUE);
175 END G_TRANSACTIONGROUPFILTER;
176
177 FUNCTION CF_SHIPPED_DATEFORMULA(TRANS_DATE IN DATE) RETURN CHAR IS
178 BEGIN
179 --character date international calendar support project
180 RETURN FND_DATE.DATE_TO_CHARDATE(dateval=>TRANS_DATE,calendar_aware=>FND_DATE.calendar_aware_alt);
181 END CF_SHIPPED_DATEFORMULA;
182
183 FUNCTION CF_DOC_DATEFORMULA(DOC_DATE IN DATE) RETURN CHAR IS
184 BEGIN
185 --character date international calendar support project
186 RETURN FND_DATE.DATE_TO_CHARDATE(dateval=>DOC_DATE,calendar_aware=>FND_DATE.calendar_aware_alt);
187 END CF_DOC_DATEFORMULA;
188
189 FUNCTION CF_TAX_REGISTRATION_NUMBERFORM RETURN VARCHAR2 IS
190 L_DEF_TAX_REG_NO VARCHAR2(50);
191 BEGIN
192 BEGIN
193 SELECT
194 ZR.REGISTRATION_NUMBER
195 INTO L_DEF_TAX_REG_NO
196 FROM
197 ZX_PARTY_TAX_PROFILE ZPTP,
198 ZX_REGISTRATIONS ZR,
199 XLE_ASSOCIATIONS_V XAV
200 WHERE ZPTP.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID
201 AND ZR.DEFAULT_REGISTRATION_FLAG = 'Y'
202 AND ZPTP.PARTY_ID = XAV.LEGAL_PARTY_ID
203 AND ZPTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
204 AND XAV.ENTITY_ID = P_ORG_ID
205 AND XAV.ENTITY_TYPE = 'INVENTORY_ORGANIZATION';
206 RETURN L_DEF_TAX_REG_NO;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 SELECT
210 ZR.REGISTRATION_NUMBER
211 INTO L_DEF_TAX_REG_NO
212 FROM
213 XLE_ETB_PROFILES XEP,
214 ZX_PARTY_TAX_PROFILE ZPTP,
215 ZX_REGISTRATIONS ZR,
216 ORG_ORGANIZATION_DEFINITIONS OOD
217 WHERE ZPTP.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID
218 AND ZR.DEFAULT_REGISTRATION_FLAG = 'Y'
219 AND ZPTP.PARTY_ID = XEP.PARTY_ID
220 AND ZPTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
221 AND XEP.LEGAL_ENTITY_ID = OOD.LEGAL_ENTITY
222 AND XEP.MAIN_ESTABLISHMENT_FLAG = 'Y'
223 AND OOD.ORGANIZATION_ID = P_ORG_ID;
224 RETURN L_DEF_TAX_REG_NO;
225 END;
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 RETURN (NULL);
229 END CF_TAX_REGISTRATION_NUMBERFORM;
230
231 FUNCTION C_MESSAGE_NO_DATA_P RETURN VARCHAR2 IS
232 BEGIN
233 RETURN C_MESSAGE_NO_DATA;
234 END C_MESSAGE_NO_DATA_P;
235
236 END INV_INVPOPSR_XMLP_PKG;
237
[Home] [Help]