1 PACKAGE BODY WIP_WIPDJDAT_XMLP_PKG AS
2 /* $Header: WIPDJDATB.pls 120.1 2008/01/31 12:12:39 npannamp noship $ */
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 P_FND_ATTACHMENTS := 'UNION SELECT TO_NUMBER(AD.PK1_VALUE),
122 VL.DESCRIPTION Instruction,
123 ST.SHORT_TEXT Instruction_Description
124 FROM FND_DOCUMENTS_SHORT_TEXT ST,
125 FND_DOCUMENTS D,
126 FND_DOCUMENTS_VL VL,
127 FND_ATTACHED_DOCUMENTS AD
128 WHERE ST.MEDIA_ID = VL.MEDIA_ID
129 AND VL.DOCUMENT_ID = AD.DOCUMENT_ID
130 AND VL.DOCUMENT_ID = D.DOCUMENT_ID
131 AND D.USAGE_TYPE IN (''O'',''T'')
132 AND SYSDATE BETWEEN TRUNC(NVL(D.START_DATE_ACTIVE, SYSDATE))
133 AND TRUNC(NVL(D.END_DATE_ACTIVE, SYSDATE))+1
134 AND AD.ENTITY_NAME = ''WIP_DISCRETE_JOBS''
135 AND AD.PK2_VALUE = :P_ORG_ID';
136 END;
137 RETURN (TRUE);
138 END AFTERPFORM;
139
140 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
141 BEGIN
142 IF P_SORT_BY = 14 THEN
143 RETURN ('SG.SCHEDULE_GROUP_NAME, DJ.BUILD_SEQUENCE');
144 ELSE
145 RETURN ('WE.WIP_ENTITY_NAME');
146 END IF;
147 RETURN NULL;
148 END C_ORDER_BYFORMULA;
149
150 FUNCTION C_LIMITER RETURN VARCHAR2 IS
151 C_OUT VARCHAR2(200);
152 BEGIN
153 IF P_SCHEDULE_GROUP_FROM IS NOT NULL THEN
154 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
155 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME BETWEEN ''' || P_SCHEDULE_GROUP_FROM || ''' AND ''' || P_SCHEDULE_GROUP_TO || '''';
156 ELSE
157 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME >= ''' || P_SCHEDULE_GROUP_FROM || '''';
158 END IF;
159 ELSE
160 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
161 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME <= ''' || P_SCHEDULE_GROUP_TO || '''';
162 ELSE
163 C_OUT := ' ';
164 END IF;
165 END IF;
166 RETURN (C_OUT);
167 END C_LIMITER;
168 END WIP_WIPDJDAT_XMLP_PKG;
169