1 PACKAGE BODY GML_PORIRUSR_XMLP_PKG AS
2 /* $Header: PORIRUSRB.pls 120.0 2007/12/24 13:29:34 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 BEGIN
8 SELECT
9 COUNT(RECV_NO)
10 INTO CP_ROWS
11 FROM
12 IC_ITEM_MST IM,
13 PO_RECV_HDR RH,
14 PO_RECV_DTL RD,
15 IC_TRAN_PND TP
16 WHERE IM.ITEM_ID = RD.ITEM_ID
17 AND RH.RECV_ID = RD.RECV_ID
18 AND RH.DELETE_MARK = 0
19 AND TRUNC(RD.RECV_DATE) between TRUNC(P_RECV_DATE_FROM)
20 AND TRUNC(P_RECV_DATE_TO)
21 AND IM.ITEM_NO between P_ITEM_NO_FROM
22 AND P_ITEM_NO_TO
23 AND RD.TO_WHSE between P_TO_WHSE_FROM
24 AND P_TO_WHSE_TO
25 AND TP.DOC_TYPE = 'RECV'
26 AND TP.DOC_ID = RH.RECV_ID
27 AND TP.LINE_ID = RD.LINE_ID
28 AND TP.TRANS_QTY > 0;
29 CP_RECV_DATE_FROM := to_char(P_RECV_DATE_FROM ,'DD-MON-YYYY');
30 CP_RECV_DATE_TO := to_char (P_RECV_DATE_TO ,'DD-MON-YYYY');
31 EXCEPTION
32 WHEN OTHERS THEN
33 CP_ROWS := 0;
34 END;
35 RETURN (TRUE);
36 END BEFOREREPORT;
37
38 FUNCTION LOT_NOFORMULA(ITEM_ID IN NUMBER
39 ,LOT_ID IN NUMBER) RETURN VARCHAR2 IS
40 V_LOT VARCHAR2(32);
41 BEGIN
42 SELECT
43 LOT_NO
44 INTO V_LOT
45 FROM
46 IC_LOTS_MST LM
47 WHERE LM.ITEM_ID = LOT_NOFORMULA.ITEM_ID
48 AND LM.LOT_ID = LOT_NOFORMULA.LOT_ID;
49 RETURN (V_LOT);
50 RETURN NULL;
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 RETURN NULL;
54 END LOT_NOFORMULA;
55
56 FUNCTION SUBLOT_NOFORMULA(ITEM_ID IN NUMBER
57 ,LOT_ID IN NUMBER) RETURN VARCHAR2 IS
58 V_SUBLOT VARCHAR2(32);
59 BEGIN
60 SELECT
61 SUBLOT_NO
62 INTO V_SUBLOT
63 FROM
64 IC_LOTS_MST LM
65 WHERE LM.ITEM_ID = SUBLOT_NOFORMULA.ITEM_ID
66 AND LM.LOT_ID = SUBLOT_NOFORMULA.LOT_ID;
67 RETURN (V_SUBLOT);
68 RETURN NULL;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 RETURN NULL;
72 END SUBLOT_NOFORMULA;
73
74 FUNCTION SORTRETCFFORMULA RETURN VARCHAR2 IS
75 X_SORT1 VARCHAR2(300);
76 CURSOR CUR_SELECT IS
77 SELECT
78 MEANING
79 FROM
80 GEM_LOOKUP_VALUES
81 WHERE LOOKUP_CODE = P_SORT_BY
82 AND LOOKUP_TYPE = 'SORT_OPTIONS1_GEMMSPO';
83 BEGIN
84 OPEN CUR_SELECT;
85 FETCH CUR_SELECT
86 INTO X_SORT1;
87 CLOSE CUR_SELECT;
88 RETURN (X_SORT1);
89 END SORTRETCFFORMULA;
90
91 FUNCTION AFTERPFORM RETURN BOOLEAN IS
92 BEGIN
93 PRN_ROWS := 0;
94 PARAM_WHERE_CLAUSE := ' ';
95 IF (P_RECV_DATE_FROM IS NOT NULL AND P_RECV_DATE_TO IS NOT NULL) THEN
96 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND TRUNC(rd.recv_date) BETWEEN TRUNC(:p_recv_date_from) AND
97 TRUNC(:p_recv_date_to) ';
98 ELSIF (P_RECV_DATE_FROM IS NOT NULL AND P_RECV_DATE_TO IS NULL) THEN
99 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND TRUNC(rd.recv_date) >= TRUNC(:p_recv_date_from) ';
100 ELSIF (P_RECV_DATE_FROM IS NULL AND P_RECV_DATE_TO IS NOT NULL) THEN
101 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND TRUNC(rd.recv_date) <= TRUNC(:p_recv_date_to) ';
102 ELSE
103 NULL;
104 END IF;
105 IF (P_TO_WHSE_FROM IS NOT NULL AND P_TO_WHSE_TO IS NOT NULL) THEN
106 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rd.to_whse between
107 :p_to_whse_from and :p_to_whse_to ';
108 ELSIF (P_TO_WHSE_FROM IS NOT NULL AND P_TO_WHSE_TO IS NULL) THEN
109 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rd.to_whse >= :p_to_whse_from ';
110 ELSIF (P_TO_WHSE_FROM IS NULL AND P_TO_WHSE_TO IS NOT NULL) THEN
111 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rd.to_whse <= :p_to_whse_to ';
112 ELSE
113 NULL;
114 END IF;
115 IF (P_ITEM_NO_FROM IS NOT NULL AND P_ITEM_NO_TO IS NOT NULL) THEN
116 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and im.item_no between
117 :p_item_no_from and :p_item_no_to ';
118 ELSIF (P_ITEM_NO_FROM IS NOT NULL AND P_ITEM_NO_TO IS NULL) THEN
119 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and im.item_no >=:p_item_no_from ';
120 ELSIF (P_ITEM_NO_FROM IS NULL AND P_ITEM_NO_TO IS NOT NULL) THEN
121 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and im.item_no <=:p_item_no_to ';
122 ELSE
123 NULL;
124 END IF;
125 RETURN (TRUE);
126 END AFTERPFORM;
127
128 FUNCTION AFTERREPORT RETURN BOOLEAN IS
129 BEGIN
130 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
131 RETURN (TRUE);
132 END AFTERREPORT;
133
134 FUNCTION CP_ROWS_P RETURN NUMBER IS
135 BEGIN
136 RETURN CP_ROWS;
137 END CP_ROWS_P;
138
139 END GML_PORIRUSR_XMLP_PKG;
140
[Home] [Help]