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.RLM_RLMDMSIQ_XMLP_PKG

Source


1 PACKAGE BODY RLM_RLMDMSIQ_XMLP_PKG AS
2 /* $Header: RLMDMSIQB.pls 120.0 2008/01/25 09:37:40 krreddy noship $ */
3   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4     L_CURRENT_ORG_ID NUMBER;
5     L_OU_NAME VARCHAR2(240);
6     L_SHIP_TO VARCHAR2(30);
7     L_STATUS VARCHAR2(1);
8     CURSOR CUR_P_SHIP_TO(NU_SHIP_TO IN NUMBER) IS
9       SELECT
10         ACCT_SITE.ECE_TP_LOCATION_CODE
11       FROM
12         HZ_CUST_SITE_USES_ALL CUST_SITE,
13         HZ_CUST_ACCT_SITES ACCT_SITE
14       WHERE CUST_SITE.SITE_USE_CODE = L_SHIP_TO
15         AND ACCT_SITE.STATUS = L_STATUS
16         AND CUST_SITE.SITE_USE_ID = NU_SHIP_TO
17         AND ACCT_SITE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID
18         AND CUST_SITE.ORG_ID = ACCT_SITE.ORG_ID;
19     CURSOR CUR_CUST_NAME(V_CUSTOMER_ID IN NUMBER) IS
20       SELECT
21         PARTY.PARTY_NAME
22       FROM
23         HZ_PARTIES PARTY,
24         HZ_CUST_ACCOUNTS CUST_ACCT
25       WHERE PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
26         AND CUST_ACCT.CUST_ACCOUNT_ID = V_CUSTOMER_ID;
27     CURSOR CUR_SHIP_FROM(V_SHIP_FROM IN NUMBER) IS
28       SELECT
29         ORGANIZATION_CODE
30       FROM
31         ORG_ORGANIZATION_DEFINITIONS
32       WHERE ORGANIZATION_ID = V_SHIP_FROM;
33   BEGIN
34     L_SHIP_TO := 'SHIP_TO';
35     L_STATUS := 'A';
36     BEGIN
37       P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
38       /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
39     EXCEPTION
40       WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
41         /*SRW.MESSAGE(1000
42                    ,'Failed in BEFORE REPORT trigger')*/NULL;
43         RETURN (FALSE);
44     END;
45     L_CURRENT_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
46     IF (L_CURRENT_ORG_ID IS NULL AND P_ORG_ID IS NOT NULL) THEN
47       MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S'
48                                   ,P_ORG_ID => P_ORG_ID);
49       L_CURRENT_ORG_ID := P_ORG_ID;
50     END IF;
51     L_OU_NAME := FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(L_CURRENT_ORG_ID);
52     CP_DEFAULT_OU := L_OU_NAME;
53     IF (P_SHIP_FROM_ORG_ID IS NOT NULL) THEN
54       OPEN CUR_SHIP_FROM(P_SHIP_FROM_ORG_ID);
55       FETCH CUR_SHIP_FROM
56        INTO CP_P_SHIP_FROM;
57       CLOSE CUR_SHIP_FROM;
58     END IF;
59     IF (P_CUSTOMER_ID IS NOT NULL) THEN
60       OPEN CUR_CUST_NAME(P_CUSTOMER_ID);
61       FETCH CUR_CUST_NAME
62        INTO CP_P_CUSTOMER_NAME;
63       CLOSE CUR_CUST_NAME;
64     END IF;
65     IF (P_SHIP_TO_ORG_ID IS NOT NULL) THEN
66       OPEN CUR_P_SHIP_TO(P_SHIP_TO_ORG_ID);
67       FETCH CUR_P_SHIP_TO
68        INTO CP_P_SHIP_TO;
69       CLOSE CUR_P_SHIP_TO;
70     END IF;
71     IF (P_DELIVER_TO_ORG_ID IS NOT NULL) THEN
72       OPEN CUR_P_SHIP_TO(P_DELIVER_TO_ORG_ID);
73       FETCH CUR_P_SHIP_TO
74        INTO CP_P_DELIVER_TO;
75       CLOSE CUR_P_SHIP_TO;
76     END IF;
77     RETURN (TRUE);
78   END BEFOREREPORT;
79 
80   FUNCTION CUSTOMER_NUMBERFORMULA(SOLD_TO_ORG_ID IN NUMBER) RETURN CHAR IS
81     V_CUST_NUMBER VARCHAR2(30);
82   BEGIN
83     SELECT
84       CUST_ACCT.ACCOUNT_NUMBER
85     INTO V_CUST_NUMBER
86     FROM
87       HZ_CUST_ACCOUNTS CUST_ACCT
88     WHERE CUST_ACCT.CUST_ACCOUNT_ID = SOLD_TO_ORG_ID;
89     RETURN (V_CUST_NUMBER);
90   EXCEPTION
91     WHEN OTHERS THEN
92       RETURN (NULL);
93   END CUSTOMER_NUMBERFORMULA;
94 
95   FUNCTION CUSTOMER_NAMEFORMULA(SOLD_TO_ORG_ID IN NUMBER) RETURN CHAR IS
96     V_CUST_NAME VARCHAR2(50);
97   BEGIN
98     SELECT
99       SUBSTRB(PARTY.PARTY_NAME
100              ,1
101              ,50) CUSTOMER_NAME
102     INTO V_CUST_NAME
103     FROM
104       HZ_CUST_ACCOUNTS CUST_ACCT,
105       HZ_PARTIES PARTY
106     WHERE CUST_ACCT.CUST_ACCOUNT_ID = SOLD_TO_ORG_ID
107       AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
108     RETURN (V_CUST_NAME);
109   EXCEPTION
110     WHEN OTHERS THEN
111       RETURN ('UNKNOWN');
112   END CUSTOMER_NAMEFORMULA;
113 
114   FUNCTION SHIP_FROM_CODEFORMULA(SHIP_FROM_ORG_ID IN NUMBER) RETURN CHAR IS
115     V_SHIP_FROM VARCHAR2(3);
116   BEGIN
117     SELECT
118       ORGANIZATION_CODE
119     INTO V_SHIP_FROM
120     FROM
121       ORG_ORGANIZATION_DEFINITIONS HRORGS
122     WHERE SHIP_FROM_ORG_ID = HRORGS.ORGANIZATION_ID;
123     RETURN (V_SHIP_FROM);
124   EXCEPTION
125     WHEN OTHERS THEN
126       RETURN (NULL);
127   END SHIP_FROM_CODEFORMULA;
128 
129   FUNCTION SHIP_FROM_NAMEFORMULA(SHIP_FROM_ORG_ID IN NUMBER) RETURN CHAR IS
130     V_SHIP_FROM ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
131   BEGIN
132     SELECT
133       ORGANIZATION_NAME
134     INTO V_SHIP_FROM
135     FROM
136       ORG_ORGANIZATION_DEFINITIONS HRORGS
137     WHERE SHIP_FROM_ORG_ID = HRORGS.ORGANIZATION_ID;
138     RETURN (V_SHIP_FROM);
139   EXCEPTION
140     WHEN OTHERS THEN
141       RETURN (NULL);
142   END SHIP_FROM_NAMEFORMULA;
143 
144   FUNCTION CUSTOMER_ITEM_NUMBERFORMULA(ORDERED_ITEM_ID IN NUMBER) RETURN CHAR IS
145     V_VALUE VARCHAR2(50);
146   BEGIN
147     SELECT
148       CUSTOMER_ITEM_NUMBER
149     INTO V_VALUE
150     FROM
151       MTL_CUSTOMER_ITEMS MCI
152     WHERE ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID;
153     RETURN (V_VALUE);
154   EXCEPTION
155     WHEN OTHERS THEN
156       RETURN (NULL);
157   END CUSTOMER_ITEM_NUMBERFORMULA;
158 
159   FUNCTION CUSTOMER_ITEM_DESCFORMULA(ORDERED_ITEM_ID IN NUMBER) RETURN CHAR IS
160     V_VALUE VARCHAR2(240);
161   BEGIN
162     SELECT
163       CUSTOMER_ITEM_DESC
164     INTO V_VALUE
165     FROM
166       MTL_CUSTOMER_ITEMS MCI
167     WHERE ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID;
168     RETURN (V_VALUE);
169   EXCEPTION
170     WHEN OTHERS THEN
171       RETURN (NULL);
172   END CUSTOMER_ITEM_DESCFORMULA;
173 
174   FUNCTION CONCATENATED_SEGMENTSFORMULA(INVENTORY_ITEM_ID IN NUMBER
175                                        ,SHIP_FROM_ORG_ID IN NUMBER) RETURN CHAR IS
176     V_VALUE VARCHAR2(40);
177   BEGIN
178     SELECT
179       CONCATENATED_SEGMENTS
180     INTO V_VALUE
181     FROM
182       MTL_SYSTEM_ITEMS_KFV MSI
183     WHERE CONCATENATED_SEGMENTSFORMULA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
184       AND MSI.ORGANIZATION_ID = SHIP_FROM_ORG_ID;
185     RETURN (V_VALUE);
186   EXCEPTION
187     WHEN OTHERS THEN
188       RETURN (NULL);
189   END CONCATENATED_SEGMENTSFORMULA;
190 
191   FUNCTION DESCRIPTIONFORMULA(INVENTORY_ITEM_ID IN NUMBER
192                              ,SHIP_FROM_ORG_ID IN NUMBER) RETURN CHAR IS
193     V_VALUE VARCHAR2(240);
194   BEGIN
195     SELECT
196       DESCRIPTION
197     INTO V_VALUE
198     FROM
199       MTL_SYSTEM_ITEMS_KFV MSI
200     WHERE  MSI.INVENTORY_ITEM_ID =DESCRIPTIONFORMULA.INVENTORY_ITEM_ID
201       AND MSI.ORGANIZATION_ID = SHIP_FROM_ORG_ID;
202     RETURN (V_VALUE);
203   EXCEPTION
204     WHEN OTHERS THEN
205       RETURN (NULL);
206   END DESCRIPTIONFORMULA;
207 
208   FUNCTION BUCKET_TYPE_CODEFORMULA(DEMAND_BUCKET_TYPE_CODE IN VARCHAR2) RETURN CHAR IS
209     V_VALUE VARCHAR2(80);
210   BEGIN
211     SELECT
212       MEANING
213     INTO V_VALUE
214     FROM
215       FND_LOOKUPS FND
216     WHERE FND.LOOKUP_TYPE = 'RLM_DETAIL_SUBTYPE_CODE'
217       AND DEMAND_BUCKET_TYPE_CODE = FND.LOOKUP_CODE;
218     RETURN (V_VALUE);
219   EXCEPTION
220     WHEN OTHERS THEN
221       RETURN (NULL);
222   END BUCKET_TYPE_CODEFORMULA;
223 
224   FUNCTION SHIP_TO_LOCATIONFORMULA(SHIP_TO_ORG_ID3 IN NUMBER) RETURN CHAR IS
225     V_VALUE VARCHAR2(40);
226   BEGIN
227     SELECT
228       LOCATION
229     INTO V_VALUE
230     FROM
231       HZ_CUST_SITE_USES
232     WHERE SITE_USE_CODE = 'SHIP_TO'
233       AND SITE_USE_ID = SHIP_TO_ORG_ID3;
234     RETURN (V_VALUE);
235   EXCEPTION
236     WHEN OTHERS THEN
237       RETURN (NULL);
238   END SHIP_TO_LOCATIONFORMULA;
239 
240   FUNCTION INTMED_SHIP_TO_LOCATIONFORMUL(INTMED_SHIP_TO_ORG_ID IN NUMBER) RETURN CHAR IS
241     V_VALUE VARCHAR2(40);
242   BEGIN
243     IF INTMED_SHIP_TO_ORG_ID IS NOT NULL THEN
244       SELECT
245         LOCATION
246       INTO V_VALUE
247       FROM
248         HZ_CUST_SITE_USES
249       WHERE SITE_USE_CODE = 'SHIP_TO'
250         AND SITE_USE_ID = INTMED_SHIP_TO_ORG_ID;
251       RETURN (V_VALUE);
252     END IF;
253   EXCEPTION
254     WHEN OTHERS THEN
255       RETURN (NULL);
256   END INTMED_SHIP_TO_LOCATIONFORMUL;
257 
258   FUNCTION SHIP_TO_NAMEFORMULA(SHIP_TO_ORG_ID3 IN NUMBER) RETURN CHAR IS
259     V_VALUE VARCHAR2(240);
260   BEGIN
261     IF SHIP_TO_ORG_ID3 IS NOT NULL THEN
262       SELECT
263         LOC.ADDRESS1
264       INTO V_VALUE
265       FROM
266         HZ_CUST_SITE_USES_ALL CUST_SITE,
267         HZ_PARTY_SITES PARTY_SITE,
268         HZ_LOCATIONS LOC,
269         HZ_CUST_ACCT_SITES ACCT_SITE
270       WHERE CUST_SITE.SITE_USE_CODE = 'SHIP_TO'
271         AND CUST_SITE.SITE_USE_ID = SHIP_TO_ORG_ID3
272         AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
273         AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
274         AND ACCT_SITE.ORG_ID = CUST_SITE.ORG_ID
275         AND ACCT_SITE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID;
276       RETURN (V_VALUE);
277     END IF;
278   EXCEPTION
279     WHEN OTHERS THEN
280       RETURN (NULL);
281   END SHIP_TO_NAMEFORMULA;
282 
283   FUNCTION INTMED_SHIP_TO_NAMEFORMULA(INTMED_SHIP_TO_ORG_ID IN NUMBER) RETURN CHAR IS
284     V_VALUE VARCHAR2(240);
285   BEGIN
286     IF (INTMED_SHIP_TO_ORG_ID IS NOT NULL) THEN
287       SELECT
288         LOC.ADDRESS1
289       INTO V_VALUE
290       FROM
291         HZ_CUST_SITE_USES_ALL CUST_SITE,
292         HZ_PARTY_SITES PARTY_SITE,
293         HZ_LOCATIONS LOC,
294         HZ_CUST_ACCT_SITES ACCT_SITE
295       WHERE CUST_SITE.SITE_USE_CODE = 'SHIP_TO'
296         AND CUST_SITE.SITE_USE_ID = INTMED_SHIP_TO_ORG_ID
297         AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
298         AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
299         AND ACCT_SITE.ORG_ID = CUST_SITE.ORG_ID
300         AND ACCT_SITE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID;
301       RETURN (V_VALUE);
302     END IF;
303   EXCEPTION
304     WHEN OTHERS THEN
305       RETURN (NULL);
306   END INTMED_SHIP_TO_NAMEFORMULA;
307 
308   FUNCTION FCST_BUCKET_TYPE_CODEFORMULA(DEMAND_BUCKET_TYPE_CODE1 IN VARCHAR2) RETURN CHAR IS
309     V_VALUE VARCHAR2(80);
310   BEGIN
311     SELECT
312       MEANING
313     INTO V_VALUE
314     FROM
315       FND_LOOKUPS FND
316     WHERE FND.LOOKUP_TYPE = 'RLM_DETAIL_SUBTYPE_CODE'
317       AND DEMAND_BUCKET_TYPE_CODE1 = FND.LOOKUP_CODE;
318     RETURN (V_VALUE);
319   EXCEPTION
320     WHEN OTHERS THEN
321       RETURN (NULL);
322   END FCST_BUCKET_TYPE_CODEFORMULA;
323 
324   FUNCTION CUM_START_DATEFORMULA(VEH_CUS_ITEM_CUM_KEY_ID IN NUMBER) RETURN DATE IS
325     V_CUM_START DATE;
326   BEGIN
327     SELECT
328       CUM_START_DATE
329     INTO V_CUM_START
330     FROM
331       RLM_CUST_ITEM_CUM_KEYS CMKEYS
332     WHERE VEH_CUS_ITEM_CUM_KEY_ID = CMKEYS.CUM_KEY_ID;
333     RETURN (V_CUM_START);
334   EXCEPTION
335     WHEN OTHERS THEN
336       RETURN (NULL);
337   END CUM_START_DATEFORMULA;
338 
339   FUNCTION CUM_QTYFORMULA(VEH_CUS_ITEM_CUM_KEY_ID IN NUMBER) RETURN NUMBER IS
340     V_CUM_QTY NUMBER;
341   BEGIN
342     SELECT
343       CUM_QTY
344     INTO V_CUM_QTY
345     FROM
346       RLM_CUST_ITEM_CUM_KEYS CMKEYS
347     WHERE VEH_CUS_ITEM_CUM_KEY_ID = CMKEYS.CUM_KEY_ID;
348     RETURN (V_CUM_QTY);
349   EXCEPTION
350     WHEN OTHERS THEN
351       RETURN (NULL);
352   END CUM_QTYFORMULA;
353 
354   FUNCTION LAST_CUM_QTY_UPDATE_DATEFORMU(VEH_CUS_ITEM_CUM_KEY_ID IN NUMBER) RETURN DATE IS
355     V_CUM_START DATE;
356   BEGIN
357     SELECT
358       LAST_CUM_QTY_UPDATE_DATE
359     INTO V_CUM_START
360     FROM
361       RLM_CUST_ITEM_CUM_KEYS CMKEYS
362     WHERE VEH_CUS_ITEM_CUM_KEY_ID = CMKEYS.CUM_KEY_ID;
363     RETURN (V_CUM_START);
364   EXCEPTION
365     WHEN OTHERS THEN
366       RETURN (NULL);
367   END LAST_CUM_QTY_UPDATE_DATEFORMU;
368 
369   FUNCTION CF_SCHED_TYPE(SCHEDULE_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
370   BEGIN
371     DECLARE
372       X_SCHEDULE_TYPE VARCHAR2(80);
373     BEGIN
374       IF (SCHEDULE_TYPE IS NULL) THEN
375         RETURN NULL;
376       ELSE
377         SELECT
378           MEANING
379         INTO X_SCHEDULE_TYPE
380         FROM
381           FND_LOOKUPS
382         WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
383           AND LOOKUP_CODE = SCHEDULE_TYPE;
384         RETURN X_SCHEDULE_TYPE;
385       END IF;
386     EXCEPTION
387       WHEN OTHERS THEN
388         RETURN '-1';
389     END;
390     RETURN NULL;
391   END CF_SCHED_TYPE;
392 
393   FUNCTION CF_SCHED_TYPE_2(SCHEDULE_TYPE2 IN VARCHAR2) RETURN VARCHAR2 IS
394   BEGIN
395     DECLARE
396       X_SCHEDULE_TYPE VARCHAR2(80);
397     BEGIN
398       IF (SCHEDULE_TYPE2 IS NULL) THEN
402           MEANING
399         RETURN NULL;
400       ELSE
401         SELECT
403         INTO X_SCHEDULE_TYPE
404         FROM
405           FND_LOOKUPS
406         WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
407           AND LOOKUP_CODE = SCHEDULE_TYPE2;
408         RETURN X_SCHEDULE_TYPE;
409       END IF;
410     EXCEPTION
411       WHEN OTHERS THEN
412         RETURN '-1';
413     END;
414     RETURN NULL;
415   END CF_SCHED_TYPE_2;
416 
417   FUNCTION AFTERPFORM RETURN BOOLEAN IS
418   BEGIN
419     IF UPPER(P_SUM_DATE_AND_BUCKET) = 'Y' THEN
420       LP_Q1_COL1 := 'to_char(oelines.schedule_ship_date, ''DD-MON-YY HH24:MI:SS'')';
421       LP_Q1_COL2 := 'NULL';
422       LP_Q1_COL3 := '0';
423       LP_Q1_COL4 := '0';
424       LP_Q1_COL5 := 'NULL';
425       LP_Q1_COL6 := 'sum(oelines.ordered_quantity)';
426       LP_Q1_COL7 := 'NULL';
427       LP_Q1_COL8 := 'sum(oelines.shipped_quantity)';
428       LP_Q1_COL9 := 'NULL';
429       LP_Q1_COL10 := 'sum(oelines.fulfilled_quantity)';
433       LP_Q1_COL14 := 'NULL';
430       LP_Q1_COL11 := 'NULL';
431       LP_Q1_COL12 := 'NULL';
432       LP_Q1_COL13 := 'NULL';
434       LP_Q1_COL15 := 'NULL';
435       LP_Q1_COL16 := 'NULL';
436       LP_Q1_COL17 := 'NULL';
437       LP_Q2_COL1 := 'nvl(oelines.intmed_ship_to_org_id, -1)';
438       LP_Q2_COL2 := 'oelines.ship_from_org_id';
439       LP_Q2_COL3 := 'oelines.ordered_item_id';
440       LP_Q2_COL4 := 'oelines.inventory_item_id';
441       LP_Q2_COL5 := 'NULL';
442       LP_Q2_COL6 := 'NULL';
443       LP_Q2_COL7 := '0';
444       LP_Q2_COL8 := '0';
445       LP_Q2_COL9 := 'NULL';
446       LP_Q2_COL10 := 'sum(oelines.ordered_quantity)';
447       LP_Q2_COL11 := 'NULL';
448       LP_Q1_GROUP_BY := ' group by oelines.sold_to_org_id,
449                         oelines.ship_to_org_id,
450                         nvl(oelines.intmed_ship_to_org_id, -1),
451                         oelines.ship_from_org_id,
452                         oelines.inventory_item_id,
453                         oelines.ordered_item_id,' || LP_Q1_COL1 || ',oelines.demand_bucket_type_code ';
454       LP_Q2_GROUP_BY := ' group by oelines.sold_to_org_id,
455                         oelines.ship_to_org_id,
456                         nvl(oelines.intmed_ship_to_org_id, -1),
457                         oelines.ship_from_org_id,
458                         oelines.ordered_item_id,
459                         oelines.inventory_item_id,
460                         oelines.schedule_ship_date,
461                         oelines.demand_bucket_type_code ';
462     ELSIF UPPER(P_SUM_DATE_AND_BUCKET) = 'N' THEN
463       LP_Q1_COL1 := 'to_char(oelines.schedule_ship_date, ''DD-MON-YY HH24:MI:SS'')';
464       LP_Q1_COL2 := 'RLM_EXTINTERFACE_SV.GetLineStatus(schlines.line_id,oelines.line_id)';
465       LP_Q1_COL3 := 'oeheaders.order_number';
466       LP_Q1_COL4 := 'oelines.line_number';
467       LP_Q1_COL5 := 'schlines.cust_po_number';
468       LP_Q1_COL6 := 'oelines.ordered_quantity';
469       LP_Q1_COL7 := 'oelines.order_quantity_uom';
470       LP_Q1_COL8 := 'oelines.shipped_quantity';
471       LP_Q1_COL9 := 'oelines.shipping_quantity_uom';
472       LP_Q1_COL10 := 'oelines.fulfilled_quantity';
473       LP_Q1_COL11 := '''EA''';
474       LP_Q1_COL12 := 'schheaders.schedule_reference_num';
475       LP_Q1_COL13 := 'schlines.customer_job';
476       LP_Q1_COL14 := 'oelines.cust_production_seq_num';
477       LP_Q1_COL15 := 'oelines.cust_model_serial_number';
478       LP_Q1_COL16 := 'schlines.industry_attribute1';
479       LP_Q1_COL17 := 'schheaders.schedule_type';
480       LP_Q2_COL1 := 'nvl(oelines.intmed_ship_to_org_id, -1)';
481       LP_Q2_COL2 := 'oelines.ship_from_org_id';
482       LP_Q2_COL3 := 'oelines.ordered_item_id';
483       LP_Q2_COL4 := 'oelines.inventory_item_id';
484       LP_Q2_COL5 := 'RLM_EXTINTERFACE_SV.GetLineStatus(schlines.line_id,oelines.line_id)';
485       LP_Q2_COL6 := 'schheaders.schedule_type';
486       LP_Q2_COL7 := 'oeheaders.order_number';
487       LP_Q2_COL8 := 'oelines.line_number';
488       LP_Q2_COL9 := 'schlines.cust_po_number';
489       LP_Q2_COL10 := 'oelines.ordered_quantity';
490       LP_Q2_COL11 := 'oelines.order_quantity_uom';
491     END IF;
492     IF UPPER(P_SHIP_UNSHIP_BOTH) = 'S' THEN
493       LP_Q1_LINES := ' and nvl(oelines.shipped_quantity,0) > 0 ' || ' and nvl(oelines.cancelled_flag,''N'') = ''N'' ';
494     ELSIF UPPER(P_SHIP_UNSHIP_BOTH) = 'U' THEN
495       LP_Q1_LINES := ' and nvl(oelines.shipped_quantity,0) = 0 ';
496       IF UPPER(P_INCL_CANCEL_LINES) = 'N' THEN
497         LP_Q1_LINES := LP_Q1_LINES || ' and nvl(oelines.cancelled_flag,''N'') = ''N'' ';
498       ELSIF UPPER(P_INCL_CANCEL_LINES) = 'Y' THEN
499         LP_Q1_LINES := LP_Q1_LINES || ' and nvl(oelines.cancelled_flag,''N'') in (''Y'',''N'') ';
500       END IF;
501     ELSIF UPPER(P_SHIP_UNSHIP_BOTH) = 'B' THEN
502       IF UPPER(P_INCL_CANCEL_LINES) = 'N' THEN
503         LP_Q1_LINES := LP_Q1_LINES || ' and nvl(oelines.cancelled_flag,''N'') = ''N'' ';
504       ELSIF UPPER(P_INCL_CANCEL_LINES) = 'Y' THEN
505         LP_Q1_LINES := LP_Q1_LINES || ' and nvl(oelines.cancelled_flag,''N'') in (''Y'',''N'') ';
506       END IF;
507     END IF;
508     DECLARE
509       STATEMENT0 VARCHAR2(100);
510       STATEMENT1 VARCHAR2(100);
511       STATEMENT2 VARCHAR2(100);
512       STATEMENT3 VARCHAR2(100);
513       STATEMENT4 VARCHAR2(100);
514       STATEMENT5 VARCHAR2(100);
515       STATEMENT6 VARCHAR2(100);
516       STATEMENT7 VARCHAR2(100);
517     BEGIN
518       IF (P_SHIP_FROM_ORG_ID IS NOT NULL) THEN
519         STATEMENT0 := '  And oelines.ship_from_org_id = :P_SHIP_FROM_ORG_ID';
520       ELSE
521         STATEMENT0 := ' ';
522       END IF;
523       IF (P_CUSTOMER_ID IS NOT NULL) THEN
524         STATEMENT1 := '  And oelines.sold_to_org_id = :P_CUSTOMER_ID';
525       ELSE
526         STATEMENT1 := ' ';
527       END IF;
528       IF (P_DELIVER_TO_ORG_ID IS NOT NULL) THEN
529         STATEMENT2 := '  And  oelines.intmed_ship_to_org_id = :P_DELIVER_TO_ORG_ID';
530       ELSE
531         STATEMENT2 := ' ';
532       END IF;
533       IF (P_SHIP_TO_ORG_ID IS NOT NULL) THEN
534         STATEMENT3 := '  And oelines.ship_to_org_id = :P_SHIP_TO_ORG_ID';
535       ELSE
536         STATEMENT3 := ' ';
537       END IF;
538       IF (UPPER(P_CUST_OR_INV_ITEM) = 'C') THEN
539         IF (P_FROM_ITEM_ID IS NOT NULL) THEN
540           STATEMENT6 := '  And oelines.ordered_item_id >= :P_FROM_ITEM_ID';
541         ELSE
542           STATEMENT6 := ' ';
543         END IF;
544         IF (P_TO_ITEM_ID IS NOT NULL) THEN
545           STATEMENT7 := '  And oelines.ordered_item_id <= :P_TO_ITEM_ID';
546         ELSE
547           STATEMENT7 := ' ';
551           STATEMENT6 := '  And oelines.inventory_item_id >= :P_FROM_ITEM_ID';
548         END IF;
549       ELSIF (UPPER(P_CUST_OR_INV_ITEM) = 'I') THEN
550         IF (P_FROM_ITEM_ID IS NOT NULL) THEN
552         ELSE
553           STATEMENT6 := ' ';
554         END IF;
555         IF (P_TO_ITEM_ID IS NOT NULL) THEN
556           STATEMENT7 := '  And oelines.inventory_item_id <= :P_TO_ITEM_ID';
557         ELSE
558           STATEMENT7 := ' ';
559         END IF;
560       END IF;
561       P_WHERE_CLAUSE1 := STATEMENT0 || STATEMENT1 || STATEMENT2 || STATEMENT3 || STATEMENT6 || STATEMENT7;
562     END;
563     DECLARE
564       STATEMENT1 VARCHAR2(100);
565       STATEMENT2 VARCHAR2(100);
566       STATEMENT3 VARCHAR2(100);
567       STATEMENT4 VARCHAR2(100);
568     BEGIN
569       IF (P_FROM_SHIP_DATE IS NOT NULL) THEN
570         STATEMENT1 := '  And oelines.SCHEDULE_SHIP_DATE >= :P_FROM_SHIP_DATE';
571       ELSE
572         STATEMENT1 := ' ';
573       END IF;
574       IF (P_TO_SHIP_DATE IS NOT NULL) THEN
575         STATEMENT2 := '  And oelines.SCHEDULE_SHIP_DATE <= :P_TO_SHIP_DATE';
576       ELSE
577         STATEMENT2 := ' ';
578       END IF;
579       IF (P_FROM_ORDER IS NOT NULL) THEN
580         STATEMENT3 := '  And oeheaders.order_number >= :p_from_order';
581       ELSE
582         STATEMENT3 := ' ';
583       END IF;
584       IF (P_TO_ORDER IS NOT NULL) THEN
585         STATEMENT4 := '  And oeheaders.order_number <= :p_to_order';
586       ELSE
587         STATEMENT4 := ' ';
588       END IF;
589       P_WHERE_CLAUSE2 := STATEMENT1 || STATEMENT2 || STATEMENT3 || STATEMENT4;
590     END;
591     RETURN (TRUE);
592   END AFTERPFORM;
593 
594   FUNCTION AFTERREPORT RETURN BOOLEAN IS
595   BEGIN
596     BEGIN
597       /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
598     EXCEPTION
599       WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
600         /*SRW.MESSAGE(1
601                    ,'Failed in AFTER REPORT TRIGGER')*/NULL;
602         RETURN (FALSE);
603     END;
604     RETURN (TRUE);
605   END AFTERREPORT;
606 
607   FUNCTION CP_DEFAULT_OU_P RETURN VARCHAR2 IS
608   BEGIN
609     RETURN CP_DEFAULT_OU;
610   END CP_DEFAULT_OU_P;
611 
612   FUNCTION CP_P_CUSTOMER_NAME_P RETURN VARCHAR2 IS
613   BEGIN
614     RETURN CP_P_CUSTOMER_NAME;
615   END CP_P_CUSTOMER_NAME_P;
616 
617   FUNCTION CP_P_SHIP_TO_P RETURN VARCHAR2 IS
618   BEGIN
619     RETURN CP_P_SHIP_TO;
620   END CP_P_SHIP_TO_P;
621 
622   FUNCTION CP_P_SHIP_FROM_P RETURN VARCHAR2 IS
623   BEGIN
624     RETURN CP_P_SHIP_FROM;
625   END CP_P_SHIP_FROM_P;
626 
627   FUNCTION CP_P_DELIVER_TO_P RETURN VARCHAR2 IS
628   BEGIN
629     RETURN CP_P_DELIVER_TO;
630   END CP_P_DELIVER_TO_P;
631 
632 END RLM_RLMDMSIQ_XMLP_PKG;