1 PACKAGE BODY GML_POAPPSRS_XMLP_PKG AS
2 /* $Header: POAPPSRSB.pls 120.0 2007/12/24 13:27:51 nchinnam noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 PARAM_WHERE_CLAUSE := ' ';
6 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rhdr.orgn_code = NVL(:p_orgn_code,rhdr.orgn_code)';
7 IF (P_RECV_NO_FROM IS NOT NULL AND P_RECV_NO_TO IS NOT NULL) THEN
8 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rhdr.recv_id between ' || P_RECV_NO_FROM || ' and ' || P_RECV_NO_TO;
9 ELSIF (P_RECV_NO_FROM IS NOT NULL AND P_RECV_NO_TO IS NULL) THEN
10 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rhdr.recv_id >= ' || P_RECV_NO_FROM;
11 ELSIF (P_RECV_NO_FROM IS NULL AND P_RECV_NO_TO IS NOT NULL) THEN
12 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and rhdr.recv_id <= :p_recv_no_to ';
13 ELSIF (P_RECV_NO_FROM IS NULL AND P_RECV_NO_TO IS NULL) THEN
14 NULL;
15 END IF;
16 IF (P_RECV_DATE_FROM IS NOT NULL AND P_RECV_DATE_TO IS NOT NULL) THEN
17 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(rhdr.recv_date) between
18 TRUNC(:p_recv_date_from) and TRUNC(:p_recv_date_to) ';
19 ELSIF (P_RECV_DATE_FROM IS NOT NULL AND P_RECV_DATE_TO IS NULL) THEN
20 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(rhdr.recv_date) >= TRUNC(:p_recv_date_from) ';
21 ELSIF (P_RECV_DATE_FROM IS NULL AND P_RECV_DATE_TO IS NOT NULL) THEN
22 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(rhdr.recv_date) <= TRUNC(:p_recv_date_to) ';
23 ELSIF (P_RECV_DATE_FROM IS NULL AND P_RECV_DATE_TO IS NULL) THEN
24 NULL;
25 END IF;
26 IF (P_VENDOR_NO_FROM IS NOT NULL AND P_VENDOR_NO_TO IS NOT NULL) THEN
27 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and vend.vendor_no between ' || '''' || P_VENDOR_NO_FROM || '''' || ' and ' || '''' || P_VENDOR_NO_TO || '''';
28 ELSIF (P_VENDOR_NO_FROM IS NOT NULL AND P_VENDOR_NO_TO IS NULL) THEN
29 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and vend.vendor_no >= ' || '''' || P_VENDOR_NO_FROM || '''';
30 ELSIF (P_VENDOR_NO_FROM IS NULL AND P_VENDOR_NO_TO IS NOT NULL) THEN
31 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and vend.vendor_no <= ' || '''' || P_VENDOR_NO_TO || '''';
32 ELSIF (P_VENDOR_NO_FROM IS NULL AND P_VENDOR_NO_TO IS NULL) THEN
33 NULL;
34 END IF;
35 IF (P_PO_NO_FROM IS NOT NULL AND P_PO_NO_TO IS NOT NULL) THEN
36 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.po_id between ' || P_PO_NO_FROM || ' and ' || P_PO_NO_TO;
37 ELSIF (P_PO_NO_FROM IS NOT NULL AND P_PO_NO_TO IS NULL) THEN
38 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.po_id >= ' || P_PO_NO_FROM;
39 ELSIF (P_PO_NO_FROM IS NULL AND P_PO_NO_TO IS NOT NULL) THEN
40 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.po_id <= ' || P_PO_NO_TO;
41 ELSIF (P_PO_NO_FROM IS NULL AND P_PO_NO_TO IS NULL) THEN
42 NULL;
43 END IF;
44 RETURN (TRUE);
45 END AFTERPFORM;
46
47 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
48 BEGIN
49 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
50 F_recv_no_from := F_recv_no_fromFormatTrigger;
51 F_recv_no_to := F_recv_no_toFormatTrigger;
52 F_po_no_from := F_po_no_fromFormatTrigger;
53 F_po_no_to := F_po_no_toFormatTrigger;
54 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
55 RETURN (TRUE);
56 END BEFOREREPORT;
57
58 FUNCTION AFTERREPORT RETURN BOOLEAN IS
59 BEGIN
60 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
61 RETURN (TRUE);
62 END AFTERREPORT;
63
64 function F_recv_no_fromFormatTrigger return varchar2 is
65 V_RECV_NO varchar2(32);
66 begin
67 if P_RECV_NO_FROM IS NOT NULL THEN
68 SELECT RECV_NO
69 INTO V_RECV_NO
70 FROM PO_RECV_HDR
71 WHERE RECV_ID = P_RECV_NO_FROM;
72 end if;
73 return V_RECV_NO;
74 exception
75 when no_data_found then
76 return ' ';
77 end;
78
79 function F_recv_no_toFormatTrigger return varchar2 is
80 V_RECV_NO varchar2(32);
81 begin
82 if P_RECV_NO_TO IS NOT NULL THEN
83 SELECT RECV_NO
84 INTO V_RECV_NO
85 FROM PO_RECV_HDR
86 WHERE RECV_ID = P_RECV_NO_TO;
87 end if;
88 return V_RECV_NO;
89 exception
90 when no_Data_found then
91 return ' ';
92 end;
93
94 function F_po_no_fromFormatTrigger return varchar2 is
95 V_PO_NO varchar2(32);
96 begin
97 if P_PO_NO_FROM IS NOT NULL THEN
98 SELECT PO_NO
99 INTO V_PO_NO
100 FROM PO_ORDR_HDR
101 WHERE PO_ID = P_PO_NO_FROM;
102 end if;
103 return V_PO_NO;
104 exception
105 when no_Data_found then
106 return ' ';
107 end;
108
109
110 function F_po_no_toFormatTrigger return varchar2 is
111 V_PO_NO varchar2(32);
112 begin
113 if P_PO_NO_TO IS NOT NULL THEN
114 SELECT PO_NO
115 INTO V_PO_NO
116 FROM PO_ORDR_HDR
117 WHERE PO_ID = P_PO_NO_TO;
118 end if;
119 return V_PO_NO;
120 exception
121 when no_Data_found then
122 return ' ';
123 end;
124
125 END GML_POAPPSRS_XMLP_PKG;
126