eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMF_GMFLCHST_XMLP_PKG

Source


1 PACKAGE BODY GMF_GMFLCHST_XMLP_PKG AS
2 /* $Header: GMFLCHSTB.pls 120.0 2007/12/24 13:33:09 nchinnam noship $ */
3   FUNCTION AFTERPFORM RETURN BOOLEAN IS
4     CURSOR CUR_GET_CURRENCY IS
5       SELECT
6         BASE_CURRENCY_CODE
7       FROM
8         GMF_FISCAL_POLICIES
9       WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
10     L_CURRENCY VARCHAR2(15);
11 x   boolean;
12   BEGIN
13      P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
14  x:= BEFOREPFORM;
15     P_FRM_ORGN := GMF_UTILITIES_GRP.GET_ORGANIZATION_CODE(P_FROM_ORGANIZATION_ID);
16     P_TO_ORGN := GMF_UTILITIES_GRP.GET_ORGANIZATION_CODE(P_TO_ORGANIZATION_ID);
17     P_FRM_ITEM := GMF_UTILITIES_GRP.GET_ITEM_NUMBER(P_FROM_ITEM_ID
18                                                    ,P_ORGANIZATION_CONTEXT);
19     P_TO_ITEM := GMF_UTILITIES_GRP.GET_ITEM_NUMBER(P_TO_ITEM_ID
20                                                   ,P_ORGANIZATION_CONTEXT);
21     P_FROM_COST_CATEGORY := GMF_UTILITIES_GRP.GET_COST_CATEGORY(P_FROM_COST_CATEGORY_ID);
22     P_TO_COST_CATEGORY := GMF_UTILITIES_GRP.GET_COST_CATEGORY(P_TO_COST_CATEGORY_ID);
23     OPEN CUR_GET_CURRENCY;
24     FETCH CUR_GET_CURRENCY
25      INTO L_CURRENCY;
26     CLOSE CUR_GET_CURRENCY;
27     IF (L_CURRENCY = P_CURRENCY_CODE OR P_CURRENCY_CODE IS NULL) THEN
28       P_CURRENCY_CODE_1 := L_CURRENCY;
29       P_EXCHANGE_RATE_1 := 1;
30     ELSE
31       IF (P_EXCHANGE_RATE IS NULL) THEN
32         FND_MESSAGE.SET_NAME('GMF'
33                             ,'GMF_SRS_EFC_NO_XCHG');
34         /*SRW.MESSAGE(100
35                    ,FND_MESSAGE.GET)*/NULL;
36         /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
37       END IF;
38     END IF;
39     IF (P_FRM_ITEM IS NOT NULL AND P_FRM_ITEM > P_TO_ITEM AND P_FRM_ITEM IS NOT NULL) THEN
40       FND_MESSAGE.SET_NAME('GMF'
41                           ,'CM_BAD_TO_ITEM_NO');
42       /*SRW.MESSAGE(100
43                  ,FND_MESSAGE.GET)*/NULL;
44       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
45     END IF;
46     IF (P_FRM_ORGN IS NOT NULL AND P_FRM_ORGN > P_TO_ORGN AND P_TO_ORGN IS NOT NULL) THEN
47       FND_MESSAGE.SET_NAME('GMF'
48                           ,'CM_BAD_TO_ITEM_NO');
49       /*SRW.MESSAGE(100
50                  ,FND_MESSAGE.GET)*/NULL;
51       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
52     END IF;
53     IF (P_FROM_COST_CATEGORY > P_TO_COST_CATEGORY AND P_FROM_COST_CATEGORY IS NOT NULL AND P_TO_COST_CATEGORY IS NOT NULL) THEN
54       FND_MESSAGE.SET_NAME('GMF'
55                           ,'CM_BAD_TO_ITEMCOST_CLS');
56       /*SRW.MESSAGE(100
57                  ,FND_MESSAGE.GET)*/NULL;
58       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
59     END IF;
60     IF (P_FROM_LOT > P_TO_LOT AND P_FROM_LOT IS NOT NULL AND P_TO_LOT IS NOT NULL) THEN
61       FND_MESSAGE.SET_NAME('GMF'
62                           ,'GMF_LOT_RANGE_ERR');
63       /*SRW.MESSAGE(100
64                  ,FND_MESSAGE.GET)*/NULL;
65       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
66     END IF;
67     IF (P_L_FRM_DATE > P_L_TO_DATE AND P_L_FRM_DATE IS NOT NULL AND P_L_TO_DATE IS NOT NULL) THEN
68       FND_MESSAGE.SET_NAME('GMF'
69                           ,'GMF_DATE_RANGE_ERR');
70       /*SRW.MESSAGE(100
71                  ,FND_MESSAGE.GET)*/NULL;
72       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
73     END IF;
74     RETURN (TRUE);
75   END AFTERPFORM;
76   FUNCTION BEFOREPFORM RETURN BOOLEAN IS
77   BEGIN
78     P_L_FRM_DATE := FND_DATE.CANONICAL_TO_DATE(P_FRM_DATE);
79     P_L_TO_DATE := FND_DATE.CANONICAL_TO_DATE(P_TO_DATE);
80     P_L_FRM_DATE_v := to_Char(P_L_FRM_DATE,'DD-MON-YY');
81     P_L_TO_DATE_v := to_char(P_L_TO_DATE,'DD-MON-YY');
82     RETURN (TRUE);
83   END BEFOREPFORM;
84   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
85   BEGIN
86     /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
87     CP_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE_1
88                                                   ,25);
89     FND_MESSAGE.SET_NAME('GMF'
90                         ,'GMF_ADJUSTMENT');
91     CP_COST_ADJUSTMENT_TEXT := FND_MESSAGE.GET;
92     FND_MESSAGE.SET_NAME('GMF'
93                         ,'GMF_INV_ADJUSTMENT');
94     CP_INV_ADJUSTMENT_TEXT := FND_MESSAGE.GET;
95     FND_MESSAGE.SET_NAME('GMF'
96                         ,'GMF_SALES_ORDER');
97     CP_SO_TEXT := FND_MESSAGE.GET;
98     FND_MESSAGE.SET_NAME('GMF'
99                         ,'GMF_BILL_ONLY_SALES_ORDER');
100     CP_BILL_ONLY_SO_TEXT := FND_MESSAGE.GET;
101     FND_MESSAGE.SET_NAME('GMF'
102                         ,'GMF_DROP_SHIP_SALES_ORDER');
103     CP_DROP_SHIP_SO_TEXT := FND_MESSAGE.GET;
104     FND_MESSAGE.SET_NAME('GMF'
105                         ,'GMF_RECEIPT');
106     CP_RECEIPT_TEXT := FND_MESSAGE.GET;
107     FND_MESSAGE.SET_NAME('GMD'
108                         ,'GMD_SAMPLE_PROD');
109     CP_PROD_TEXT := FND_MESSAGE.GET;
110     FND_MESSAGE.SET_NAME('GMD'
111                         ,'GMF_TRANSFER');
112     CP_XFER_TEXT := FND_MESSAGE.GET;
113     RETURN (TRUE);
114   END BEFOREREPORT;
115   FUNCTION CF_SRC_TRANSFORMULA(DOC_ID IN NUMBER
116                               ,LINE_ID IN NUMBER
117                               ,DOC_TYPE IN VARCHAR2
118                               ,ORGANIZATION_CODE IN VARCHAR2) RETURN CHAR IS
119     L_TEXT VARCHAR2(100) := NULL;
120     CURSOR CUR_GET_BTCH IS
121       SELECT
122         PLANT_CODE || ' ' || BATCH_NO
123       FROM
124         GME_BATCH_HEADER
125       WHERE BATCH_ID = DOC_ID;
126     CURSOR CUR_GET_RCV IS
127       SELECT
128         DISTINCT
129         MP.ORGANIZATION_CODE || ' ' || R.RECEIPT_NUM
130       FROM
131         RCV_SHIPMENT_HEADERS R,
132         MTL_MATERIAL_TRANSACTIONS P,
133         MTL_PARAMETERS MP
134       WHERE R.SHIPMENT_HEADER_ID = P.TRANSACTION_SOURCE_ID
135         AND R.ORGANIZATION_ID = P.ORGANIZATION_ID
136         AND P.TRANSACTION_SOURCE_ID = DOC_ID
137         AND P.TRX_SOURCE_LINE_ID = LINE_ID
138         AND MP.ORGANIZATION_ID = P.ORGANIZATION_ID;
139     CURSOR CUR_GET_INV IS
140       SELECT
141         DISTINCT
142         J.ORGN_CODE || ' ' || J.JOURNAL_NO
143       FROM
144         IC_JRNL_MST J,
145         IC_ADJS_JNL D
146       WHERE D.TRANS_TYPE = DOC_TYPE
147         AND D.DOC_ID = DOC_ID
148         AND J.JOURNAL_ID = D.JOURNAL_ID;
149     CURSOR CUR_GET_CYCL IS
150       SELECT
151         MP.ORGANIZATION_CODE || ' ' || MCC.COUNT_LIST_SEQUENCE
152       FROM
153         MTL_MATERIAL_TRANSACTIONS MTT,
154         MTL_PARAMETERS MP,
155         MTL_CYCLE_COUNT_ENTRIES MCC
156       WHERE MTT.CYCLE_COUNT_ID = DOC_ID
157         AND MP.ORGANIZATION_ID = MTT.ORGANIZATION_ID
158         AND MCC.CYCLE_COUNT_ENTRY_ID = MTT.CYCLE_COUNT_ID;
159     CURSOR CUR_GET_XFER IS
160       SELECT
161         MP.ORGANIZATION_CODE
162       FROM
163         MTL_MATERIAL_TRANSACTIONS MMT,
164         MTL_PARAMETERS MP
165       WHERE MMT.TRANSFER_TRANSACTION_ID = DOC_ID
166         AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID;
167     CURSOR C_SO_DOC(P_DOC_ID IN NUMBER,P_LEGAL_ENTITY_ID IN NUMBER,P_LINE_ID IN NUMBER) IS
168       SELECT
169         DISTINCT
170         MP.ORGANIZATION_CODE || ' ' || D.NAME
171       FROM
172         WSH_NEW_DELIVERIES D,
173         MTL_MATERIAL_TRANSACTIONS T,
174         WSH_DELIVERY_DETAILS WD,
175         WSH_DELIVERY_ASSIGNMENTS WA,
176         MTL_PARAMETERS MP,
177         MTL_TXN_SOURCE_TYPES MTST,
178         ORG_ORGANIZATION_DEFINITIONS OOD
179       WHERE D.DELIVERY_ID = WA.DELIVERY_ID
180         AND WA.DELIVERY_DETAIL_ID = WD.DELIVERY_DETAIL_ID
181         AND OOD.LEGAL_ENTITY = P_LEGAL_ENTITY_ID
182         AND T.TRANSACTION_SOURCE_ID = P_DOC_ID
183         AND T.TRX_SOURCE_LINE_ID = P_LINE_ID
184         AND MTST.TRANSACTION_SOURCE_TYPE_NAME = 'Sales Order'
185         AND MTST.TRANSACTION_SOURCE_TYPE_ID = T.TRANSACTION_SOURCE_TYPE_ID
186         AND T.ORGANIZATION_ID = MP.ORGANIZATION_ID
187         AND OOD.ORGANIZATION_ID = T.ORGANIZATION_ID;
188     CURSOR C_BILL_ONLY_SO_DOC(P_DOC_ID IN NUMBER,P_LEGAL_ENTITY_ID IN VARCHAR2,P_LINE_ID IN NUMBER) IS
189       SELECT
190         DISTINCT
191         '(Bill-Only) ' || MP.ORGANIZATION_CODE || ' ' || OH.ORDER_NUMBER
192       FROM
193         MTL_PARAMETERS MP,
194         MTL_MATERIAL_TRANSACTIONS T,
195         OE_ORDER_HEADERS_ALL OH,
196         OE_ORDER_LINES_ALL OL,
197         MTL_TXN_SOURCE_TYPES MTST,
198         ORG_ORGANIZATION_DEFINITIONS OOD
199       WHERE OOD.LEGAL_ENTITY = P_LEGAL_ENTITY_ID
200         AND MTST.TRANSACTION_SOURCE_TYPE_NAME = 'Sales Order'
201         AND T.TRANSACTION_SOURCE_ID = P_DOC_ID
202         AND T.TRX_SOURCE_LINE_ID = P_LINE_ID
203         AND T.TRX_SOURCE_LINE_ID = OL.LINE_ID
204         AND OH.HEADER_ID = OL.HEADER_ID
205         AND OOD.ORGANIZATION_ID = T.ORGANIZATION_ID
206         AND MTST.TRANSACTION_SOURCE_TYPE_ID = T.TRANSACTION_SOURCE_TYPE_ID;
207     CURSOR C_DROP_SHIP_SO_DOC(P_DOC_ID IN NUMBER,P_LEGAL_ENTITY_ID IN VARCHAR2,P_LINE_ID IN NUMBER) IS
208       SELECT
209         DISTINCT
210         '(Drop-Ship) ' || OOD.ORGANIZATION_CODE || ' ' || OH.ORDER_NUMBER
211       FROM
212         MTL_MATERIAL_TRANSACTIONS T,
213         OE_ORDER_HEADERS_ALL OH,
214         OE_ORDER_LINES_ALL OL,
215         MTL_TXN_SOURCE_TYPES MTST,
216         ORG_ORGANIZATION_DEFINITIONS OOD
217       WHERE OOD.LEGAL_ENTITY = P_LEGAL_ENTITY_ID
218         AND MTST.TRANSACTION_SOURCE_TYPE_NAME = 'Sales Order'
219         AND T.TRANSACTION_SOURCE_ID = P_DOC_ID
220         AND T.TRX_SOURCE_LINE_ID = P_LINE_ID
221         AND T.TRX_SOURCE_LINE_ID = OL.LINE_ID
222         AND OH.HEADER_ID = OL.HEADER_ID
223         AND OOD.ORGANIZATION_ID = T.ORGANIZATION_ID
224         AND MTST.TRANSACTION_SOURCE_TYPE_ID = T.TRANSACTION_SOURCE_TYPE_ID
225         AND EXISTS (
226         SELECT
227           1
228         FROM
229           OE_DROP_SHIP_SOURCES S
230         WHERE OL.LINE_ID = S.LINE_ID
231           AND OL.HEADER_ID = S.HEADER_ID );
232     CURSOR CUR_GET_BOL_NO(P_LINE_ID IN NUMBER) IS
233       SELECT
234         DISTINCT
235         B.ORGN_CODE || ' ' || B.BOL_NO
236       FROM
237         OP_ORDR_DTL D,
238         OP_BILL_LAD B
239       WHERE D.LINE_ID = P_LINE_ID
240         AND D.BOL_ID = B.BOL_ID;
241   BEGIN
242     IF (DOC_TYPE = 'PROD') THEN
243       OPEN CUR_GET_BTCH;
244       FETCH CUR_GET_BTCH
245        INTO L_TEXT;
246       CLOSE CUR_GET_BTCH;
247       L_TEXT := CP_PROD_TEXT || ',' || L_TEXT;
248     ELSIF (DOC_TYPE = 'PORC') THEN
249       OPEN CUR_GET_RCV;
250       FETCH CUR_GET_RCV
251        INTO L_TEXT;
252       CLOSE CUR_GET_RCV;
253       L_TEXT := CP_RECEIPT_TEXT || ',' || L_TEXT;
254     ELSIF (DOC_TYPE in ('CREI','ADJI','TRNI','ADJR','CRER','TRNR')) THEN
255       OPEN CUR_GET_INV;
256       FETCH CUR_GET_INV
257        INTO L_TEXT;
258       CLOSE CUR_GET_INV;
259       L_TEXT := CP_INV_ADJUSTMENT_TEXT || ',' || L_TEXT;
260     ELSIF (DOC_TYPE in ('PIPC','PIPH','PICY')) THEN
261       OPEN CUR_GET_CYCL;
262       FETCH CUR_GET_CYCL
263        INTO L_TEXT;
264       CLOSE CUR_GET_CYCL;
265       L_TEXT := CP_INV_ADJUSTMENT_TEXT || ',' || L_TEXT;
266     ELSIF (DOC_TYPE in ('OPSO')) THEN
267       OPEN CUR_GET_BOL_NO(LINE_ID);
268       FETCH CUR_GET_BOL_NO
269        INTO L_TEXT;
270       CLOSE CUR_GET_BOL_NO;
271       L_TEXT := CP_SO_TEXT || ',' || L_TEXT;
272     ELSIF (DOC_TYPE = 'OMSO') THEN
273       OPEN C_SO_DOC(DOC_ID,P_LEGAL_ENTITY_ID,LINE_ID);
274       FETCH C_SO_DOC
275        INTO L_TEXT;
276       IF C_SO_DOC%NOTFOUND THEN
277         CLOSE C_SO_DOC;
278         OPEN C_BILL_ONLY_SO_DOC(DOC_ID,P_LEGAL_ENTITY_ID,LINE_ID);
279         FETCH C_BILL_ONLY_SO_DOC
280          INTO L_TEXT;
281         IF C_BILL_ONLY_SO_DOC%NOTFOUND THEN
282           CLOSE C_BILL_ONLY_SO_DOC;
283           OPEN C_DROP_SHIP_SO_DOC(DOC_ID,P_LEGAL_ENTITY_ID,LINE_ID);
284           FETCH C_DROP_SHIP_SO_DOC
285            INTO L_TEXT;
286           CLOSE C_DROP_SHIP_SO_DOC;
287           L_TEXT := CP_DROP_SHIP_SO_TEXT || ',' || L_TEXT;
288         ELSE
289           CLOSE C_BILL_ONLY_SO_DOC;
290           L_TEXT := CP_BILL_ONLY_SO_TEXT || ',' || L_TEXT;
291         END IF;
292       ELSE
293         CLOSE C_SO_DOC;
294         L_TEXT := CP_SO_TEXT || ',' || L_TEXT;
295       END IF;
296     ELSIF (DOC_TYPE = 'XFER') THEN
297       OPEN CUR_GET_XFER;
298       FETCH CUR_GET_XFER
299        INTO L_TEXT;
300       CLOSE CUR_GET_XFER;
301       L_TEXT := CP_XFER_TEXT || ',' || L_TEXT;
302     ELSIF (DOC_TYPE = 'LADJ') THEN
303       L_TEXT := CP_COST_ADJUSTMENT_TEXT || ',' || ORGANIZATION_CODE || ' ' || LINE_ID;
304     ELSE
305       L_TEXT := DOC_TYPE || ',' || ORGANIZATION_CODE;
306     END IF;
307     RETURN (L_TEXT);
308   END CF_SRC_TRANSFORMULA;
309   FUNCTION CF_SRC_ONHAND_VALUEFORMULA(NEW_UNIT_COST IN NUMBER
310                                      ,NEW_ONHAND_QTY IN NUMBER) RETURN CHAR IS
311   BEGIN
312     RETURN (TO_CHAR(NEW_UNIT_COST * NEW_ONHAND_QTY
313                   ,CP_FORMAT_MASK));
314   END CF_SRC_ONHAND_VALUEFORMULA;
315   FUNCTION CF_LEGAL_ENTITYFORMULA RETURN CHAR IS
316     L_LEGAL_ENTITY_NAME VARCHAR2(240);
317   BEGIN
318     BEGIN
319       SELECT
320         LEGAL_ENTITY_NAME
321       INTO L_LEGAL_ENTITY_NAME
322       FROM
323         GMF_LEGAL_ENTITIES
324       WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
325     EXCEPTION
326       WHEN OTHERS THEN
327         L_LEGAL_ENTITY_NAME := NULL;
328     END;
329     RETURN (L_LEGAL_ENTITY_NAME);
330   END CF_LEGAL_ENTITYFORMULA;
331   FUNCTION CF_COST_MTHDFORMULA RETURN VARCHAR2 IS
332     L_COST_MTHD_CODE VARCHAR2(10);
333   BEGIN
334     BEGIN
335       SELECT
336         COST_MTHD_CODE
337       INTO L_COST_MTHD_CODE
338       FROM
339         CM_MTHD_MST
340       WHERE COST_TYPE_ID = P_COST_TYPE_ID;
341     EXCEPTION
342       WHEN OTHERS THEN
343         L_COST_MTHD_CODE := NULL;
344     END;
345     RETURN (L_COST_MTHD_CODE);
346   END CF_COST_MTHDFORMULA;
347   FUNCTION AFTERREPORT RETURN BOOLEAN IS
348   BEGIN
349     /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
350     RETURN (TRUE);
351   END AFTERREPORT;
352   FUNCTION CP_INV_ADJUSTMENT_TEXT_P RETURN VARCHAR2 IS
353   BEGIN
354     RETURN CP_INV_ADJUSTMENT_TEXT;
355   END CP_INV_ADJUSTMENT_TEXT_P;
356   FUNCTION CP_COST_ADJUSTMENT_TEXT_P RETURN VARCHAR2 IS
357   BEGIN
358     RETURN CP_COST_ADJUSTMENT_TEXT;
359   END CP_COST_ADJUSTMENT_TEXT_P;
360   FUNCTION CP_SO_TEXT_P RETURN VARCHAR2 IS
361   BEGIN
362     RETURN CP_SO_TEXT;
363   END CP_SO_TEXT_P;
364   FUNCTION CP_BILL_ONLY_SO_TEXT_P RETURN VARCHAR2 IS
365   BEGIN
366     RETURN CP_BILL_ONLY_SO_TEXT;
367   END CP_BILL_ONLY_SO_TEXT_P;
368   FUNCTION CP_DROP_SHIP_SO_TEXT_P RETURN VARCHAR2 IS
369   BEGIN
370     RETURN CP_DROP_SHIP_SO_TEXT;
371   END CP_DROP_SHIP_SO_TEXT_P;
372   FUNCTION CP_RECEIPT_TEXT_P RETURN VARCHAR2 IS
373   BEGIN
374     RETURN CP_RECEIPT_TEXT;
375   END CP_RECEIPT_TEXT_P;
376   FUNCTION CP_PROD_TEXT_P RETURN VARCHAR2 IS
377   BEGIN
378     RETURN CP_PROD_TEXT;
379   END CP_PROD_TEXT_P;
380   FUNCTION CP_FORMAT_MASK_P RETURN VARCHAR2 IS
381   BEGIN
382     RETURN CP_FORMAT_MASK;
383   END CP_FORMAT_MASK_P;
384   FUNCTION CP_XFER_TEXT_P RETURN VARCHAR2 IS
385   BEGIN
386     RETURN CP_XFER_TEXT;
387   END CP_XFER_TEXT_P;
388 END GMF_GMFLCHST_XMLP_PKG;
389