1 PACKAGE BODY WIP_WIPDJDAT_XMLP_PKG AS
2 /* $Header: WIPDJDATB.pls 120.1.12020000.2 2012/07/18 07:45:55 ankohli ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 SELECT fifst.id_flex_num
6 into p_item_flex_num
7 FROM fnd_id_flex_structures fifst
8 WHERE fifst.application_id = 401
9 AND fifst.id_flex_code = 'MSTK'
10 AND fifst.enabled_flag = 'Y'
11 AND fifst.freeze_flex_definition_flag = 'Y'
12 and rownum<2;
13 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
14 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
15 DECLARE
16 P_ORG_ID_CHAR VARCHAR2(100) := (P_ORG_ID);
17 BEGIN
18 FND_PROFILE.PUT('MFG_ORGANIZATION_ID'
19 ,P_ORG_ID_CHAR);
20 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
21 EXCEPTION
22 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
23 /*SRW.MESSAGE(020
24 ,'Failed in before report trigger, setting org profile ')*/NULL;
25 RAISE;
26 END;
27 IF (P_FROM_ASSEMBLY IS NOT NULL) THEN
28 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
29 NULL;
30 ELSE
31 NULL;
32 END IF;
33 ELSE
34 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
35 NULL;
36 END IF;
37 END IF;
38 RETURN (TRUE);
39 END BEFOREREPORT;
40
41 FUNCTION AFTERREPORT RETURN BOOLEAN IS
42 BEGIN
43 BEGIN
44 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
45 END;
46 RETURN (TRUE);
47 END AFTERREPORT;
48
49 FUNCTION JOB_LIMITER RETURN CHARACTER IS
50 LIMIT_JOBS VARCHAR2(768);
51 BEGIN
52 IF (P_FROM_JOB IS NOT NULL) THEN
53 IF (P_TO_JOB IS NOT NULL) THEN
54 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME BETWEEN ''' || REPLACE(P_FROM_JOB
55 ,''''
56 ,'''''') || ''' AND ''' || REPLACE(P_TO_JOB
57 ,''''
58 ,'''''') || '''';
59 ELSE
60 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME >= ''' || REPLACE(P_FROM_JOB
61 ,''''
62 ,'''''') || '''';
63 END IF;
64 ELSE
65 IF (P_TO_JOB IS NOT NULL) THEN
66 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME <= ''' || REPLACE(P_TO_JOB
67 ,''''
68 ,'''''') || '''';
69 ELSE
70 LIMIT_JOBS := ' ';
71 END IF;
72 END IF;
73 RETURN (LIMIT_JOBS);
74 END JOB_LIMITER;
75
76 FUNCTION DATE_LIMITER RETURN CHARACTER IS
77 LIMIT_DATES VARCHAR2(120);
78 BEGIN
79 IF (P_FROM_START_DATE IS NOT NULL) THEN
80 IF (P_TO_START_DATE IS NOT NULL) THEN
81 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) BETWEEN TO_DATE(''' || TO_CHAR(P_FROM_START_DATE
82 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND TO_DATE(''' || TO_CHAR(P_TO_START_DATE
83 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
84 ELSE
85 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) >= TO_DATE(''' || TO_CHAR(P_FROM_START_DATE
86 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
87 END IF;
88 ELSE
89 IF (P_TO_START_DATE IS NOT NULL) THEN
90 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) <= TO_DATE(''' || TO_CHAR(P_TO_START_DATE
91 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
92 ELSE
93 LIMIT_DATES := ' ';
94 END IF;
95 END IF;
96 RETURN (LIMIT_DATES);
97 END DATE_LIMITER;
98
99 FUNCTION C_ASSEMBLY_LIMITERFORMULA RETURN VARCHAR2 IS
100 BEGIN
101 BEGIN
102 IF (P_ITEM_WHERE IS NOT NULL) THEN
103 RETURN ('AND ');
104 ELSE
105 RETURN (' ');
106 END IF;
107 END;
108 RETURN NULL;
109 END C_ASSEMBLY_LIMITERFORMULA;
110
111 FUNCTION AFTERPFORM RETURN BOOLEAN IS
112 BEGIN
113 BEGIN
114 IF P_FROM_ASSEMBLY IS NOT NULL OR P_TO_ASSEMBLY IS NOT NULL THEN
115 P_OUTER := ' ';
116 END IF;
117 IF P_SCHEDULE_GROUP_FROM IS NOT NULL OR P_SCHEDULE_GROUP_TO IS NOT NULL THEN
118 P_SG_OUTER := ' ';
119 END IF;
120 P_OE_ONT_INSTALLED := OE_INSTALL.GET_ACTIVE_PRODUCT;
121
122 /* Bug 13492275 - tekang - altered P_FND_ATTACHMENTS to include wip_discrete_jobs and wip_entities table
123 and where clause linking discrete jobs and attached documents table so that job and date limiters
124 can be used on comments */
125
126
127 P_FND_ATTACHMENTS := 'UNION SELECT TO_NUMBER(AD.PK1_VALUE),
128 VL.DESCRIPTION Instruction,
129 ST.SHORT_TEXT Instruction_Description
130 FROM FND_DOCUMENTS_SHORT_TEXT ST,
131 FND_DOCUMENTS D,
132 FND_DOCUMENTS_VL VL,
133 FND_ATTACHED_DOCUMENTS AD,
134 WIP_DISCRETE_JOBS DJ,
135 WIP_ENTITIES WE
136 WHERE ST.MEDIA_ID = VL.MEDIA_ID
137 AND VL.DOCUMENT_ID = AD.DOCUMENT_ID
138 AND VL.DOCUMENT_ID = D.DOCUMENT_ID
139 AND D.USAGE_TYPE IN (''O'',''T'')
140 AND SYSDATE BETWEEN TRUNC(NVL(D.START_DATE_ACTIVE, SYSDATE))
141 AND TRUNC(NVL(D.END_DATE_ACTIVE, SYSDATE))+1
142 AND AD.ENTITY_NAME = ''WIP_DISCRETE_JOBS''
143 AND AD.PK2_VALUE = :P_ORG_ID
144 AND DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
145 AND DJ.WIP_ENTITY_ID = AD.PK1_VALUE
146 AND DJ.WIP_ENTITY_ID = :WIP_Entity_ID';
147
148 /* Bug 13492275 - tekang - added another parameter P_ITEM_ATTACHMENTs that will get short text notes that were
149 migrated from the items */
150
151 P_ITEM_ATTACHMENTS := 'UNION SELECT TO_NUMBER(AD.PK2_VALUE),
152 VL.DESCRIPTION Instruction,
153 ST.SHORT_TEXT Instruction_Description
154 FROM FND_DOCUMENTS_SHORT_TEXT ST,
155 FND_DOCUMENTS D,
156 FND_DOCUMENTS_VL VL,
157 FND_ATTACHED_DOCUMENTS AD,
158 WIP_DISCRETE_JOBS DJ,
159 WIP_ENTITIES WE
160 WHERE ST.MEDIA_ID = VL.MEDIA_ID
161 AND VL.DOCUMENT_ID = AD.DOCUMENT_ID
162 AND VL.DOCUMENT_ID = D.DOCUMENT_ID
163 AND D.USAGE_TYPE IN (''O'',''T'')
164 AND SYSDATE BETWEEN TRUNC(NVL(D.START_DATE_ACTIVE, SYSDATE))
165 AND TRUNC(NVL(D.END_DATE_ACTIVE, SYSDATE))+1
166 AND AD.ENTITY_NAME = ''MTL_SYSTEM_ITEMS''
167 AND AD.PK1_VALUE = :P_ORG_ID
168 AND DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
169 AND DJ.PRIMARY_ITEM_ID = AD.PK2_VALUE
170 AND DJ.WIP_ENTITY_ID = :WIP_Entity_ID';
171 END;
172 RETURN (TRUE);
173 END AFTERPFORM;
174
175 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
176 BEGIN
177 IF P_SORT_BY = 14 THEN
178 RETURN ('SG.SCHEDULE_GROUP_NAME, DJ.BUILD_SEQUENCE');
179 ELSE
180 RETURN ('WE.WIP_ENTITY_NAME');
181 END IF;
182 RETURN NULL;
183 END C_ORDER_BYFORMULA;
184
185 FUNCTION C_LIMITER RETURN VARCHAR2 IS
186 C_OUT VARCHAR2(200);
187 BEGIN
188 IF P_SCHEDULE_GROUP_FROM IS NOT NULL THEN
189 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
190 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME BETWEEN ''' || P_SCHEDULE_GROUP_FROM || ''' AND ''' || P_SCHEDULE_GROUP_TO || '''';
191 ELSE
192 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME >= ''' || P_SCHEDULE_GROUP_FROM || '''';
193 END IF;
194 ELSE
195 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
196 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME <= ''' || P_SCHEDULE_GROUP_TO || '''';
197 ELSE
198 C_OUT := ' ';
199 END IF;
200 END IF;
201 RETURN (C_OUT);
202 END C_LIMITER;
203 END WIP_WIPDJDAT_XMLP_PKG;
204
[Home] [Help]