1 PACKAGE BODY JA_JAIN57F4_XMLP_PKG AS
2 /* $Header: JAIN57F4B.pls 120.1 2007/12/25 16:08:35 dwkrishn noship $ */
3 FUNCTION CF_1FORMULA(CANCEL_FLAG IN VARCHAR2
4 ,PRINT_ORIGINAL IN VARCHAR2) RETURN CHARACTER IS
5 BEGIN
6 IF NVL(CANCEL_FLAG
7 ,'N') = 'Y' THEN
8 RETURN ('Cancelled');
9 ELSIF NVL(PRINT_ORIGINAL
10 ,'N') = 'Y' THEN
11 RETURN ('Original');
12 ELSE
13 RETURN ('Duplicate');
14 END IF;
15 END CF_1FORMULA;
16
17 FUNCTION AFTERREPORT RETURN BOOLEAN IS
18 CURSOR C_COUNT_UPDATE IS
19 SELECT
20 FORM_ID,
21 PRINT_CNT
22 FROM
23 JAI_PO_OSP_HDRS J57F4
24 WHERE TRUNC(J57F4.ISSUE_DATE) <= NVL(P_TO_DATE
25 ,TRUNC(SYSDATE))
26 AND TRUNC(J57F4.ISSUE_DATE) >= TRUNC(NVL(P_FROM_DATE
27 ,TRUNC(J57F4.ISSUE_DATE)))
28 AND TRUNC(J57F4.FORM_ID) >= NVL(P_FORM_ID_FROM
29 ,TRUNC(J57F4.FORM_ID))
30 AND TRUNC(J57F4.FORM_ID) <= NVL(P_FORM_ID_TO
31 ,TRUNC(J57F4.FORM_ID))
32 AND UPPER(J57F4.ISSUE_APPROVED) = 'Y'
33 AND J57F4.VENDOR_ID = NVL(P_VENDOR_ID
34 ,J57F4.VENDOR_ID)
35 AND ( J57F4.PRINT_ORIGINAL = 'Y'
36 OR J57F4.PRINT_DUPLICATE = 'Y' );
37 V_COUNT NUMBER;
38 V_FORM_ID NUMBER;
39 BEGIN
40 FOR c_cnt_upd IN C_COUNT_UPDATE LOOP
41 V_COUNT := NVL(C_CNT_UPD.PRINT_CNT
42 ,0) + 1;
43 V_FORM_ID := C_CNT_UPD.FORM_ID;
44 UPDATE
45 JAI_PO_OSP_HDRS
46 SET
47 PRINT_CNT = V_COUNT
48 ,PRINT_ORIGINAL = 'N'
49 ,PRINT_DUPLICATE = 'N'
50 WHERE FORM_ID = V_FORM_ID;
51 END LOOP;
52 COMMIT;
53 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
54 RETURN (TRUE);
55 END AFTERREPORT;
56
57 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
58 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
59 SELECT
60 CONCURRENT_PROGRAM_ID,
61 NVL(ENABLE_TRACE
62 ,'N')
63 FROM
64 FND_CONCURRENT_REQUESTS
65 WHERE REQUEST_ID = P_REQUEST_ID;
66 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
67 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
68 BEGIN
69 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
70 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
71 /*SRW.MESSAGE(1275
72 ,'Report Version is 120.3 Last modified date is 21/12/2006')*/NULL;
73 BEGIN
74 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
75 FETCH C_PROGRAM_ID
76 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
77 CLOSE C_PROGRAM_ID;
78 /*SRW.MESSAGE(1275
79 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
80 IF V_ENABLE_TRACE = 'Y' THEN
81 EXECUTE IMMEDIATE
82 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
83 END IF;
84 EXCEPTION
85 WHEN OTHERS THEN
86 /*SRW.MESSAGE(1275
87 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
88 END;
89 RETURN (TRUE);
90 END BEFOREREPORT;
91
92 FUNCTION CF_VENDOR_SITE_NAMEFORMULA(VENDOR_ID_v IN NUMBER
93 ,VENDOR_SITE_ID_v IN NUMBER) RETURN CHAR IS
94 V_EC_CODE JAI_CMN_VENDOR_SITES.EC_CODE%TYPE;
95 BEGIN
96 FOR c_fetch_ec_code IN (SELECT
97 EC_CODE
98 FROM
99 JAI_CMN_VENDOR_SITES
100 WHERE VENDOR_ID = VENDOR_ID_v
101 AND VENDOR_SITE_ID = VENDOR_SITE_ID_v) LOOP
102 V_EC_CODE := C_FETCH_EC_CODE.EC_CODE;
103 END LOOP;
104 IF V_EC_CODE IS NULL THEN
105 RETURN (NULL);
106 ELSE
107 RETURN (V_EC_CODE);
108 END IF;
109 END CF_VENDOR_SITE_NAMEFORMULA;
110
111 FUNCTION CF_PO_NUMBERFORMULA(PO_HEADER_ID IN NUMBER) RETURN CHAR IS
112 LC_PO_NUMBER VARCHAR2(20);
113 CURSOR LCU_GET_PO_NUMBER(P_PO_HEADER_ID IN NUMBER) IS
114 SELECT
115 PHA.SEGMENT1
116 FROM
117 PO_HEADERS_ALL PHA
118 WHERE PHA.PO_HEADER_ID = P_PO_HEADER_ID;
119 BEGIN
120 IF (PO_HEADER_ID IS NOT NULL) THEN
121 OPEN LCU_GET_PO_NUMBER(PO_HEADER_ID);
122 FETCH LCU_GET_PO_NUMBER
123 INTO LC_PO_NUMBER;
124 CLOSE LCU_GET_PO_NUMBER;
125 END IF;
126 RETURN (LC_PO_NUMBER);
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF (LCU_GET_PO_NUMBER%ISOPEN) THEN
130 CLOSE LCU_GET_PO_NUMBER;
131 END IF;
132 RETURN (NULL);
133 END CF_PO_NUMBERFORMULA;
134
135 FUNCTION CF_RELEASE_NUMBERFORMULA(PO_RELEASE_ID IN NUMBER) RETURN NUMBER IS
136 LN_RELEASE_NUMBER NUMBER;
137 CURSOR LCU_GET_RELEASE_NUMBER(P_RELEASE_ID IN NUMBER) IS
138 SELECT
139 PRA.RELEASE_NUM
140 FROM
141 PO_RELEASES_ALL PRA
142 WHERE PRA.PO_RELEASE_ID = P_RELEASE_ID;
143 BEGIN
144 IF (PO_RELEASE_ID IS NOT NULL) THEN
145 OPEN LCU_GET_RELEASE_NUMBER(P_RELEASE_ID => PO_RELEASE_ID);
146 FETCH LCU_GET_RELEASE_NUMBER
147 INTO LN_RELEASE_NUMBER;
148 CLOSE LCU_GET_RELEASE_NUMBER;
149 END IF;
150 RETURN (LN_RELEASE_NUMBER);
151 EXCEPTION
152 WHEN OTHERS THEN
153 IF (LCU_GET_RELEASE_NUMBER%ISOPEN) THEN
154 CLOSE LCU_GET_RELEASE_NUMBER;
155 END IF;
156 RETURN (NULL);
157 END CF_RELEASE_NUMBERFORMULA;
158
159 FUNCTION CF_JOB_NAMEFORMULA(WIP_ENTITY_ID IN NUMBER
160 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
161 LC_JOB_NAME VARCHAR2(240);
162 CURSOR LCU_GET_JOB_NAME(P_WIP_ENTITY_ID IN NUMBER,P_ORGANIZATION_ID IN NUMBER) IS
163 SELECT
164 WE.WIP_ENTITY_NAME
165 FROM
166 WIP_ENTITIES WE
167 WHERE WE.WIP_ENTITY_ID = P_WIP_ENTITY_ID
168 AND WE.ORGANIZATION_ID = P_ORGANIZATION_ID;
169 BEGIN
170 IF (WIP_ENTITY_ID IS NOT NULL) THEN
171 OPEN LCU_GET_JOB_NAME(P_WIP_ENTITY_ID => WIP_ENTITY_ID,P_ORGANIZATION_ID => ORGANIZATION_ID);
172 FETCH LCU_GET_JOB_NAME
173 INTO LC_JOB_NAME;
174 CLOSE LCU_GET_JOB_NAME;
175 END IF;
176 RETURN LC_JOB_NAME;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF (LCU_GET_JOB_NAME%ISOPEN) THEN
180 CLOSE LCU_GET_JOB_NAME;
181 END IF;
182 RETURN (NULL);
183 END CF_JOB_NAMEFORMULA;
184
185 FUNCTION CF_JOB_START_DATEFORMULA(WIP_ENTITY_ID IN NUMBER
186 ,ORGANIZATION_ID IN NUMBER) RETURN DATE IS
187 LD_JOB_START_DATE DATE;
188 CURSOR LCU_GET_JOB_START_DATE(P_WIP_ENTITY_ID IN NUMBER,P_ORGANIZATION_ID IN NUMBER) IS
189 SELECT
190 WDJ.SCHEDULED_START_DATE
191 FROM
192 WIP_DISCRETE_JOBS WDJ
193 WHERE WDJ.WIP_ENTITY_ID = P_WIP_ENTITY_ID
194 AND WDJ.ORGANIZATION_ID = P_ORGANIZATION_ID;
195 BEGIN
196 IF (WIP_ENTITY_ID IS NOT NULL) THEN
197 OPEN LCU_GET_JOB_START_DATE(P_WIP_ENTITY_ID => WIP_ENTITY_ID,P_ORGANIZATION_ID => ORGANIZATION_ID);
198 FETCH LCU_GET_JOB_START_DATE
199 INTO LD_JOB_START_DATE;
200 CLOSE LCU_GET_JOB_START_DATE;
201 END IF;
202 RETURN LD_JOB_START_DATE;
203 EXCEPTION
204 WHEN OTHERS THEN
205 IF (LCU_GET_JOB_START_DATE%ISOPEN) THEN
206 CLOSE LCU_GET_JOB_START_DATE;
207 END IF;
208 RETURN (NULL);
209 END CF_JOB_START_DATEFORMULA;
210
211 FUNCTION CF_ASSEMBLY_ITEM_CODEFORMULA(ASSEMBLY_ID IN NUMBER
212 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
213 LC_ASSEMBLY_ITEM_CODE VARCHAR2(40);
214 CURSOR LCU_GET_ASSEMBLY_ITEM_CODE(P_ASSEMBLY_ID IN NUMBER,P_ORGANIZATION_ID IN NUMBER) IS
215 SELECT
216 MSIK.CONCATENATED_SEGMENTS
217 FROM
218 MTL_SYSTEM_ITEMS_KFV MSIK
219 WHERE MSIK.INVENTORY_ITEM_ID = P_ASSEMBLY_ID
220 AND MSIK.ORGANIZATION_ID = P_ORGANIZATION_ID;
221 BEGIN
222 IF (ASSEMBLY_ID IS NOT NULL) THEN
223 OPEN LCU_GET_ASSEMBLY_ITEM_CODE(P_ASSEMBLY_ID => ASSEMBLY_ID,P_ORGANIZATION_ID => ORGANIZATION_ID);
224 FETCH LCU_GET_ASSEMBLY_ITEM_CODE
225 INTO LC_ASSEMBLY_ITEM_CODE;
226 CLOSE LCU_GET_ASSEMBLY_ITEM_CODE;
227 END IF;
228 RETURN (LC_ASSEMBLY_ITEM_CODE);
229 EXCEPTION
230 WHEN OTHERS THEN
231 IF (LCU_GET_ASSEMBLY_ITEM_CODE%ISOPEN) THEN
232 CLOSE LCU_GET_ASSEMBLY_ITEM_CODE;
233 END IF;
234 RETURN (NULL);
235 END CF_ASSEMBLY_ITEM_CODEFORMULA;
236
237 END JA_JAIN57F4_XMLP_PKG;
238
239
[Home] [Help]