1 PACKAGE BODY WIP_WIPREJVR_XMLP_PKG AS
2 /* $Header: WIPREJVRB.pls 120.1 2008/01/31 12:36:51 npannamp noship $ */
3 FUNCTION DISP_CURRENCYFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 RETURN (REPORT_OPTION || ' (' || CURRENCY_CODE || ')');
6 END DISP_CURRENCYFORMULA;
7
8 FUNCTION ORG_NAME_HDRFORMULA RETURN VARCHAR2 IS
9 BEGIN
10 RETURN (ORG_NAME);
11 END ORG_NAME_HDRFORMULA;
12
13 FUNCTION TOT_CST_INC_APP_CSTFORMULA(TOT_ACT_ISS_STD IN NUMBER
14 ,TOT_RES_APP_COST IN NUMBER
15 ,TOT_RES_OVR_APP_COST IN NUMBER
16 ,TOT_MV_OVR_APP_COST IN NUMBER) RETURN NUMBER IS
17 BEGIN
18 /*SRW.REFERENCE(TOT_ACT_ISS_STD)*/NULL;
19 /*SRW.REFERENCE(TOT_RES_APP_COST)*/NULL;
20 /*SRW.REFERENCE(TOT_RES_OVR_APP_COST)*/NULL;
21 /*SRW.REFERENCE(TOT_MV_OVR_APP_COST)*/NULL;
22 RETURN (NVL(TOT_ACT_ISS_STD
23 ,0) + NVL(TOT_RES_APP_COST
24 ,0) + NVL(TOT_RES_OVR_APP_COST
25 ,0) + NVL(TOT_MV_OVR_APP_COST
26 ,0));
27 END TOT_CST_INC_APP_CSTFORMULA;
28
29 FUNCTION TOT_JOB_BALANCE_CSTFORMULA(TOT_CST_INC_APP_CST IN NUMBER
30 ,TOT_SCP_AND_COMP_CST IN NUMBER
31 ,TOT_CLOSE_TRX_CST IN NUMBER) RETURN NUMBER IS
32 BEGIN
33 /*SRW.REFERENCE(TOT_CST_INC_APP_CST)*/NULL;
34 /*SRW.REFERENCE(TOT_SCP_AND_COMP_CST)*/NULL;
35 /*SRW.REFERENCE(TOT_CLOSE_TRX_CST)*/NULL;
36 RETURN (NVL(TOT_CST_INC_APP_CST
37 ,0) + NVL(TOT_SCP_AND_COMP_CST
38 ,0) + NVL(TOT_CLOSE_TRX_CST
39 ,0));
40 END TOT_JOB_BALANCE_CSTFORMULA;
41
42 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
43 BEGIN
44 BEGIN
45 SELECT fifst.id_flex_num
46 into p_item_flex_num
47 FROM fnd_id_flex_structures fifst
48 WHERE fifst.application_id = 401
49 AND fifst.id_flex_code = 'MSTK'
50 AND fifst.enabled_flag = 'Y'
51 AND fifst.freeze_flex_definition_flag = 'Y'
52 and rownum<2;
53 LPER_SCHD_CLS_DATE:=to_char(PER_SCHD_CLS_DATE,'DD-MON-YY');
54 LPER_START_DATE:=to_char(PER_START_DATE,'DD-MON-YY');
55 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
56 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
57 SELECT
58 OOD.ORGANIZATION_NAME,
59 SOB.CURRENCY_CODE,
60 FC.EXTENDED_PRECISION,
61 FC.PRECISION,
62 RPT_SORT_OPT.MEANING,
63 RPT_RUN_OPT.MEANING,
64 CLS_TYPE.MEANING
65 INTO ORG_NAME,CURRENCY_CODE,EXT_PRECISION,PRECISION,REPORT_SORT,REPORT_OPTION,CLASS_TYPE_NAME
66 FROM
67 FND_CURRENCIES FC,
68 GL_SETS_OF_BOOKS SOB,
69 ORG_ORGANIZATION_DEFINITIONS OOD,
70 MFG_LOOKUPS RPT_SORT_OPT,
71 MFG_LOOKUPS RPT_RUN_OPT,
72 MFG_LOOKUPS CLS_TYPE
73 WHERE OOD.ORGANIZATION_ID = ORG_ID
74 AND OOD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
75 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
76 AND FC.ENABLED_FLAG = 'Y'
77 AND RPT_SORT_OPT.LOOKUP_TYPE = 'CST_WIP_REPORT_SORT'
78 AND RPT_SORT_OPT.LOOKUP_CODE = REPORT_SORT_OPT
79 AND CLS_TYPE.LOOKUP_TYPE = 'WIP_CLASS_TYPE'
80 AND CLS_TYPE.LOOKUP_CODE = 4
81 AND RPT_RUN_OPT.LOOKUP_TYPE = 'CST_BICR_DETAIL_OPTION'
82 AND RPT_RUN_OPT.LOOKUP_CODE = REPORT_RUN_OPT;
83 IF STATUS_TYPE IS NULL THEN
84 STATUS_TYPE_NAME := '';
85 ELSE
86 SELECT
87 STS_TYPE.MEANING
88 INTO STATUS_TYPE_NAME
89 FROM
90 MFG_LOOKUPS STS_TYPE
91 WHERE STS_TYPE.LOOKUP_TYPE = 'WIP_JOB_STATUS'
92 AND STS_TYPE.LOOKUP_CODE = STATUS_TYPE;
93 END IF;
94 IF (JOB_FROM IS NULL) AND (JOB_TO IS NULL) THEN
95 WHERE_JOB := '1 = 1';
96 ELSIF (JOB_FROM IS NULL) AND (JOB_TO IS NOT NULL) THEN
97 WHERE_JOB := 'wp.wip_entity_name <= ''' || JOB_TO || '''';
98 ELSIF (JOB_FROM IS NOT NULL) AND (JOB_TO IS NULL) THEN
99 WHERE_JOB := 'wp.wip_entity_name >= ''' || JOB_FROM || '''';
100 ELSE
101 WHERE_JOB := 'wp.wip_entity_name BETWEEN ''' || JOB_FROM || ''' AND ''' || JOB_TO || '''';
102 END IF;
103 IF (CLASS_FROM IS NULL) AND (CLASS_TO IS NULL) THEN
104 WHERE_CLASS := '1 = 1';
105 ELSIF (CLASS_FROM IS NULL) AND (CLASS_TO IS NOT NULL) THEN
106 WHERE_CLASS := 'wdj.class_code <= ''' || CLASS_TO || '''';
107 ELSIF (CLASS_FROM IS NOT NULL) AND (CLASS_TO IS NULL) THEN
108 WHERE_CLASS := 'wdj.class_code >= ''' || CLASS_FROM || '''';
109 ELSE
110 WHERE_CLASS := 'wdj.class_code BETWEEN ''' || CLASS_FROM || ''' AND ''' || CLASS_TO || '''';
111 END IF;
112 IF REPORT_SORT_OPT = 1 THEN
113 REPORT_SORT_BY_BEF := 'wp.wip_entity_name, ';
114 REPORT_SORT_BY_AFT := ' ';
115 ELSIF REPORT_SORT_OPT = 2 THEN
116 REPORT_SORT_BY_BEF := ' ';
117 REPORT_SORT_BY_AFT := ', wp.wip_entity_name';
118 ELSIF REPORT_SORT_OPT = 3 THEN
119 REPORT_SORT_BY_BEF := 'ml_class_type.meaning, ';
120 REPORT_SORT_BY_AFT := ', wp.wip_entity_name';
121 END IF;
122 IF (P_FROM_ITEM IS NOT NULL) THEN
123 IF (P_TO_ITEM IS NOT NULL) THEN
124 NULL;
125 ELSE
126 NULL;
127 END IF;
128 ELSE
129 IF (P_TO_ITEM IS NOT NULL) THEN
130 NULL;
131 ELSE
132 P_WHERE_ITEM := '1 = 1';
133 END IF;
134 END IF;
135 END;
136 RETURN (TRUE);
137 END BEFOREREPORT;
138
139 FUNCTION AFTERREPORT RETURN BOOLEAN IS
140 BEGIN
141 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
142 RETURN (TRUE);
143 END AFTERREPORT;
144
145 FUNCTION AFTERPFORM RETURN BOOLEAN IS
146 BEGIN
147 IF P_FROM_ITEM IS NOT NULL OR P_TO_ITEM IS NOT NULL THEN
148 P_OUTER := ' ';
149 END IF;
150 IF SUBMITTED_BY = 'SRS' THEN
151 P_SUBSELECT := 'and (:SUBMITTED_BY = ''SRS''
152 and exists
153 (select ''x''
154 from wip_period_balances wpb
155 where wpb.wip_entity_id = wp.wip_entity_id
156 and wpb.organization_id = wp.organization_id
157 and wpb.acct_period_id IN
158 (select oap.acct_period_id
159 from org_acct_periods oap
160 where trunc(oap.period_start_date) >= :PER_START_DATE
161 and trunc(oap.schedule_close_date) <= :PER_SCHD_CLS_DATE
162 and oap.organization_id = wp.organization_id))
163
164 ) ';
165 END IF;
166 IF SUBMITTED_BY = 'PLS' THEN
167 P_SUBSELECT := 'and (:SUBMITTED_BY = ''PLS'' and
168 wp.wip_entity_id in
169 (select wip_entity_id
170 from wip_dj_close_temp
171 where group_id = :GROUP_ID)
172 )
173 ';
174 END IF;
175 RETURN (TRUE);
176 END AFTERPFORM;
177
178 FUNCTION ORG_NAME_P RETURN VARCHAR2 IS
179 BEGIN
180 RETURN ORG_NAME;
181 END ORG_NAME_P;
182
183 FUNCTION PRECISION_P RETURN NUMBER IS
184 BEGIN
185 RETURN PRECISION;
186 END PRECISION_P;
187
188 FUNCTION EXT_PRECISION_P RETURN NUMBER IS
189 BEGIN
190 RETURN EXT_PRECISION;
191 END EXT_PRECISION_P;
192
193 FUNCTION CURRENCY_CODE_P RETURN VARCHAR2 IS
194 BEGIN
195 RETURN CURRENCY_CODE;
196 END CURRENCY_CODE_P;
197
198 FUNCTION REPORT_OPTION_P RETURN VARCHAR2 IS
199 BEGIN
200 RETURN REPORT_OPTION;
201 END REPORT_OPTION_P;
202
203 FUNCTION STATUS_TYPE_NAME_P RETURN VARCHAR2 IS
204 BEGIN
205 RETURN STATUS_TYPE_NAME;
206 END STATUS_TYPE_NAME_P;
207
208 FUNCTION REPORT_SORT_BY_AFT_P RETURN VARCHAR2 IS
209 BEGIN
210 RETURN REPORT_SORT_BY_AFT;
211 END REPORT_SORT_BY_AFT_P;
212
213 FUNCTION CLASS_TYPE_NAME_P RETURN VARCHAR2 IS
214 BEGIN
215 RETURN CLASS_TYPE_NAME;
216 END CLASS_TYPE_NAME_P;
217
218 FUNCTION WHERE_JOB_P RETURN VARCHAR2 IS
219 BEGIN
220 RETURN WHERE_JOB;
221 END WHERE_JOB_P;
222
223 FUNCTION REPORT_SORT_BY_BEF_P RETURN VARCHAR2 IS
224 BEGIN
225 RETURN REPORT_SORT_BY_BEF;
226 END REPORT_SORT_BY_BEF_P;
227
228 FUNCTION WHERE_CLASS_P RETURN VARCHAR2 IS
229 BEGIN
230 RETURN WHERE_CLASS;
231 END WHERE_CLASS_P;
232
233 FUNCTION REPORT_SORT_P RETURN VARCHAR2 IS
234 BEGIN
235 RETURN REPORT_SORT;
236 END REPORT_SORT_P;
237
238 END WIP_WIPREJVR_XMLP_PKG;
239
[Home] [Help]