1 PACKAGE MSC_CALENDAR AUTHID CURRENT_USER AS
2 /* $Header: MSCCALDS.pls 120.4 2008/01/04 11:01:56 sbnaik ship $ */
3
4 --------PROFILE OPTION VALUES ------------------------------------------
5 G_VAR_BKT_REFERENCE_CALENDAR VARCHAR2(14) := NVL(FND_PROFILE.Value('MSC_BKT_REFERENCE_CALENDAR'),'-23453');
6
7 FUNCTION NEXT_WORK_DAY(arg_org_id IN NUMBER,
8 arg_instance_id IN NUMBER,
9 arg_bucket IN NUMBER,
10 arg_date IN DATE) RETURN DATE;
11 FUNCTION PREV_WORK_DAY(arg_org_id IN NUMBER,
12 arg_instance_id IN NUMBER,
13 arg_bucket IN NUMBER,
14 arg_date IN DATE) RETURN DATE;
15 FUNCTION DATE_OFFSET( arg_org_id IN NUMBER,
16 arg_instance_id IN NUMBER,
17 arg_bucket IN NUMBER,
18 arg_date IN DATE,
19 arg_offset IN NUMBER) RETURN DATE;
20 FUNCTION DAYS_BETWEEN( arg_org_id IN NUMBER,
21 arg_instance_id IN NUMBER,
22 arg_bucket IN NUMBER,
23 arg_date1 IN DATE,
24 arg_date2 IN DATE) RETURN NUMBER;
25 PROCEDURE SELECT_CALENDAR_DEFAULTS( arg_org_id IN NUMBER,
26 arg_instance_id IN NUMBER,
27 arg_calendar_code OUT NOCOPY VARCHAR2,
28 arg_exception_set_id OUT NOCOPY NUMBER);
29
30 FUNCTION PREV_DELIVERY_CALENDAR_DAY (arg_calendar_code IN VARCHAR2,
31 arg_instance_id IN NUMBER,
32 arg_exception_set_id IN NUMBER,
33 arg_date IN DATE,
34 arg_bucket IN NUMBER) RETURN DATE;
35
36 FUNCTION CALENDAR_NEXT_WORK_DAY(arg_instance_id IN NUMBER,
37 arg_calendar_code IN VARCHAR2,
38 arg_bucket IN NUMBER,
39 arg_date IN DATE) RETURN DATE;
40
41 FUNCTION CALENDAR_DAYS_BETWEEN( arg_instance_id IN NUMBER,
42 arg_calendar_code IN VARCHAR2,
43 arg_bucket IN NUMBER,
44 arg_date1 IN DATE,
45 arg_date2 IN DATE) RETURN NUMBER;
46
47 TYPE_DAILY_BUCKET CONSTANT NUMBER := 1;
48 TYPE_WEEKLY_BUCKET CONSTANT NUMBER := 2;
49 TYPE_MONTHLY_BUCKET CONSTANT NUMBER := 3;
50
51 /* Global Variables added for ship_rec_cal project */
52 SMC CONSTANT INTEGER := 1;
53 SSC CONSTANT INTEGER := 2;
54 ORC CONSTANT INTEGER := 3;
55 OMC CONSTANT INTEGER := 4;
56 OSC CONSTANT INTEGER := 5;
57 CRC CONSTANT INTEGER := 6;
58 VIC CONSTANT INTEGER := 7;
59 FOC CONSTANT VARCHAR2(3) := '@@@';
60 G_RETAIN_DATE VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MRP_RETAIN_DATES_WTIN_CAL_BOUNDARY'), 'N');
61
62 -- New functions added for ship_rec_cal project.
63 FUNCTION Get_Calendar_Code(
64 p_instance_id IN number,
65 p_plan_id IN number,
66 p_inventory_item_id IN number,
67 p_partner_id IN number,
68 p_partner_site_id IN number,
69 p_partner_type IN number,
70 p_organization_id IN number,
71 p_ship_method_code IN varchar2,
72 p_calendar_type IN integer
73 ) RETURN VARCHAR2;
74
75 FUNCTION Get_Calendar_Code(
76 p_instance_id IN number,
77 p_plan_id IN number,
78 p_inventory_item_id IN number,
79 p_partner_id IN number,
80 p_partner_site_id IN number,
81 p_partner_type IN number,
82 p_organization_id IN number,
83 p_ship_method_code IN varchar2,
84 p_calendar_type IN integer,
85 p_association_type OUT NOCOPY NUMBER
86 ) RETURN VARCHAR2;
87
88 FUNCTION Get_Calendar_Code(
89 p_instance_id IN number,
90 p_plan_id IN number,
91 p_inventory_item_id IN number,
92 p_partner_id IN number,
93 p_partner_site_id IN number,
94 p_partner_type IN number,
95 p_organization_id IN number,
96 p_ship_method_code IN varchar2,
97 p_calendar_type IN integer,
98 p_from_cal_window IN integer,
99 p_association_type OUT NOCOPY NUMBER
100 ) RETURN VARCHAR2;
101
102 -- New Overloaded Functions added for ship_rec_cal project driven by calendar_code rather than org_id
103 FUNCTION NEXT_WORK_DAY(
104 p_calendar_code IN varchar2,
105 p_instance_id IN number,
106 p_calendar_date IN date
107 ) RETURN DATE;
108
109 FUNCTION PREV_WORK_DAY(
110 p_calendar_code IN varchar2,
111 p_instance_id IN number,
112 p_calendar_date IN date
113 ) RETURN DATE;
114
115 FUNCTION DATE_OFFSET(
116 p_calendar_code IN varchar2,
117 p_instance_id IN number,
118 p_calendar_date IN date,
119 p_days_offset IN number,
120 p_offset_type IN number
121 ) RETURN DATE;
122
123 FUNCTION THREE_STEP_CAL_OFFSET_DATE(
124 p_input_date IN Date,
125 p_first_cal_code IN VARCHAR2,
126 p_first_cal_validation_type IN NUMBER,
127 p_second_cal_code IN VARCHAR2,
128 p_offset_days IN NUMBER,
129 p_second_cal_validation_type IN NUMBER,
130 p_third_cal_code IN VARCHAR2,
131 p_third_cal_validation_type IN NUMBER,
132 p_instance_id IN NUMBER
133 ) RETURN DATE;
134
135
136 END MSC_CALENDAR;