1 PACKAGE BODY RCV_OE_RMA_RECEIPTS_SV AS
2 /* $Header: RCVRMARB.pls 120.0.12000000.2 2007/04/10 06:36:20 kagupta ship $*/
3
4
5 /*===========================================================================
6
7 FUNCTION NAME: rma_val_receipt_date_tolerance()
8
9 DESCRIPTION:
10
11 Checks that the receipt date is within the receipt date
12 tolerance.
13
14 PARAMETERS:
15
16 Parameter IN/OUT Datatype Description
17 --------------- -------- ------------ --------------------------------------
18
19 x_oe_order_header_id IN NUMBER RMA header id
20
21 x_oe_order_line_id IN NUMBER RMA line id
22
23 x_receipt_date IN DATE Receipt Date to be validated.
24
25 RETURN VALUE: TRUE if receipt date is within tolerance
26 FALSE otherwise.
27
28 DESIGN REFERENCES: RCVRCERC.dd x
29 RCVTXECO.dd _/
30 RCVTXERE.dd x
31 RCVTXERT.dd x
32
33 CHANGE HISTORY:
34 ===========================================================================*/
35
36
37
38 FUNCTION rma_val_receipt_date_tolerance (x_oe_order_header_id IN NUMBER,
39 x_oe_order_line_id IN NUMBER,
40 x_receipt_date IN DATE) RETURN BOOLEAN IS
41
42 /*
43 ** Function determines if the receipt date falls within the receipt date
44 ** tolerance window. If it does, the function returns a value of TRUE,
45 ** otherwise it returns a value of FALSE.
46 */
47
48 x_progress VARCHAR2(3) := '001';
49 x_earliest_acceptable_date DATE := sysdate;
50 x_latest_acceptable_date DATE := sysdate;
51
52 BEGIN
53
54 x_progress := '010';
55
56 SELECT EARLIEST_ACCEPTABLE_DATE,
57 LATEST_ACCEPTABLE_DATE
58 INTO x_earliest_acceptable_date,
59 x_latest_acceptable_date
60 FROM oe_order_lines_all --1561179
61 WHERE header_id = x_oe_order_header_id
62 AND line_id = x_oe_order_line_id;
63
64 x_progress := '020';
65
66 /* bug 1362426 : added 'trunc' to all the dates because the dates include time and
67 this was causing the condition to always return false */
68
69 /* Bug 3543872 : If either of or both earliest_acceptable_date and latest_acceptable_date
70 were null and the receipt date was other than sysdate we were always returning
71 false. So modified the if clause to have proper validations on the receipt date
72 tolerance.
73
74 The validation works as follows:
75
76 earliest_acceptable_date latest_acceptable_date return true if
77
78 null null return true
79 null not null receipt date <= latest_acceptable_date
80 not null null receipt_date >= earliest_acceptable_date
81 not null not null receipt_date between earliest_acceptable_
82 date and latest_acceptable_date.
83
84 */
85
86
87 IF (trunc(x_earliest_acceptable_date) is null and trunc(x_latest_acceptable_date) is null)
88 OR (trunc(x_earliest_acceptable_date) is null
89 and (nvl(trunc(x_receipt_date), trunc(sysdate)) <= nvl(trunc(x_latest_acceptable_date),trunc(sysdate))))
90 OR (trunc(x_latest_acceptable_date) is null
91 and (nvl(trunc(x_receipt_date), trunc(sysdate)) >= nvl(trunc(x_earliest_acceptable_date),trunc(sysdate))))
92 OR ( (nvl(trunc(x_receipt_date), trunc(sysdate)) >= nvl(trunc(x_earliest_acceptable_date),trunc(sysdate)))
93 and
94 (nvl(trunc(x_receipt_date), trunc(sysdate)) <= nvl(trunc(x_latest_acceptable_date),trunc(sysdate))) )
95 THEN
96 return (true);
97 ELSE
98 return (false);
99 END IF;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 po_message_s.sql_error('rma_val_receipt_date_tolerance', x_progress,sqlcode);
104 RAISE;
105
106 END rma_val_receipt_date_tolerance;
107
108 /*===========================================================================
109
110 PROCEDURE NAME: rma_get_org_info()
111
112 ===========================================================================*/
113
114 /* <R12 MOAC START>
115 ** Changed the signature of the following procedure rma_get_org_info.
116 ** The procedure now has only 2 parameters.
117 ** The procedure returns the org_id for a given oe_order_line_id.
118 */
119
120 PROCEDURE rma_get_org_info (x_new_org_id OUT NOCOPY NUMBER,
121 X_oe_order_line_id IN NUMBER) IS
122 BEGIN
123
124 /* For Bug 5958418 Getting the org information from OM table rather than from work flow tables.
125 select number_value
126 into x_new_org_id
127 from wf_item_attribute_values
128 where item_key = to_char(X_oe_order_line_id)
129 and item_type='OEOL'
130 and name='ORG_ID';
131 */
132 select org_id
133 into x_new_org_id
134 from oe_order_lines_all
135 where line_id = X_oe_order_line_id;
136
137 END rma_get_org_info;
138
139 /* <R12 MOAC END> */
140
141 END RCV_OE_RMA_RECEIPTS_SV;
[Home] [Help]