1 PACKAGE BODY RCV_INTRANSIT_SV as
2 /* $Header: RCVSHINB.pls 120.1 2006/06/19 07:38:00 rahujain noship $ */
3
4 /*========================= RCV_INTRANSIT_SV =============================*/
5 /*===========================================================================
6
7 FUNCTION NAME: get_expected_shipped_date
8
9 ===========================================================================*/
10 FUNCTION get_expected_shipped_date (
11 X_from_organization_id IN NUMBER,
12 X_to_organization_id IN NUMBER,
13 X_need_by_date IN DATE,
14 X_req_line_id IN NUMBER)
15 RETURN DATE IS
16
17 X_intransit_time NUMBER;
18 X_expected_shipped_date DATE;
19 X_progress VARCHAR2(4) := '000';
20
21 BEGIN
22
23 /*
24 ** Get the mtl_interorg_ship_methods row for the from and to org id
25 ** combination. This will provide you with the intransit lead time
26 */
27
28 BEGIN
29
30 /* Bug 1246475 - GMudgal - 23-MAR-00
31 ** Added condition for default_flag.
32 ** Default_flag :1, Yes, 2, No.
33 ** If there is more than one shipping method defined between
34 ** two orgs, then the following query used to fail with no
35 ** data found and the intransit time wouldn't be subtracted
36 ** from the need by date.
37 **
38 ** Not sure if Inventory makes it mandatory to select one and
39 ** only one method as the default when creating the ship methods.
40 */
41
42 SELECT MSM.INTRANSIT_TIME
43 INTO X_INTRANSIT_TIME
44 FROM MTL_INTERORG_SHIP_METHODS MSM
45 WHERE MSM.FROM_ORGANIZATION_ID = X_from_organization_id
46 AND MSM.TO_ORGANIZATION_ID = X_to_organization_id
47 AND MSM.DEFAULT_FLAG=1;
48
49
50 /*
51 ** Calculate the expected shipped date based on ship to org
52 ** work calendar
53 */
54 /* Cannot do this based on the pragma requirements
55 ** Will have to check with MRP to change their function pragmas
56 */
57 /*
58 X_expected_shipped_date :=
59 MRP_CALENDAR.DATE_OFFSET(X_TO_ORGANIZATION_ID,
60 1,
61 X_need_by_date,
62 X_INTRANSIT_TIME);
63 */
64 X_expected_shipped_date := X_need_by_date - X_INTRANSIT_TIME;
65
66 EXCEPTION
67
68 /*Bug# 1881765
69 ** If more than one row exists for the MTL_INTERORG_SHIP_METHODS then
70 take the first shipment Method from the set of defined methods
71 Else If no row exists for the MTL_INTERORG_SHIP_METHODS then just
72 set the expected shipped date to the need by date.
73 */
74
75 WHEN NO_DATA_FOUND THEN
76
77 BEGIN
78
79 SELECT MSM.INTRANSIT_TIME
80 INTO X_INTRANSIT_TIME
81 FROM MTL_INTERORG_SHIP_METHODS MSM
82 WHERE MSM.FROM_ORGANIZATION_ID = X_from_organization_id
83 AND MSM.TO_ORGANIZATION_ID = X_to_organization_id
84 AND ROWNUM=1;
85
86 X_expected_shipped_date := X_need_by_date - X_INTRANSIT_TIME;
87
88 EXCEPTION
89
90 WHEN NO_DATA_FOUND THEN
91
92 X_expected_shipped_date := X_need_by_date;
93
94 END;
95
96 WHEN OTHERS THEN
97
98 X_expected_shipped_date := X_need_by_date;
99 END;
100
101 RETURN (X_expected_shipped_date);
102
103 END get_expected_shipped_date;
104
105 /*===========================================================================
106
107 FUNCTION NAME: rcv_get_org_name
108
109 ===========================================================================*/
110
111 FUNCTION rcv_get_org_name (
112 p_source_code IN VARCHAR2,
113 p_vendor_id IN NUMBER,
114 p_org_id IN NUMBER)
115 RETURN VARCHAR2 IS
116 -- v_Result VARCHAR(80);
117 /** <UTF8 FPI> **/
118 /** tpoon 9/27/2002 **/
119 /** Changed v_Result to use %TYPE **/
120 v_Result hr_all_organization_units.name%TYPE;
121 BEGIN
122
123 BEGIN
124
125 IF (p_source_code = 'VENDOR') THEN
126
127 SELECT MAX(NVL(VENDOR_NAME, NULL))
128 INTO v_Result
129 FROM PO_VENDORS
130 WHERE vendor_id = p_vendor_id;
131
132 ELSE
133 --Bug 5217526. Fetch Org Name from HR_ORGANIZATION_UNITS
134 SELECT MAX(NVL(NAME, NULL))
135 INTO v_Result
136 FROM HR_ORGANIZATION_UNITS
137 WHERE ORGANIZATION_ID = p_org_id;
138 END IF;
139
140
141 EXCEPTION
142
143 WHEN OTHERS THEN
144 v_Result := 'RCV_GET_ORG_NAME-> ' || to_char(p_vendor_id) || ' ' || to_char(p_org_id);
145
146 END;
147
148 RETURN v_Result;
149
150 END RCV_GET_ORG_NAME;
151
152 END RCV_INTRANSIT_SV;
[Home] [Help]