1 PACKAGE AHL_MR_LOOP_CHAIN_RELNS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVLCRS.pls 120.0.12020000.2 2012/12/10 13:44:34 shnatu noship $ */
3 /* Define Record Type for Affected Item Instance Record */
4 TYPE MR_ITEM_INSTANCE_REC_TYPE
5 IS
6 RECORD
7 (
8 ITEM_NUMBER VARCHAR2(40),
9 SERIAL_NUMBER VARCHAR2(30),
10 LOCATION VARCHAR2(4000),
11 STATUS VARCHAR2(80),
12 OWNER VARCHAR2(360),
13 CONDITION VARCHAR2(240),
14 UNIT_NAME VARCHAR2(80),
15 ITEM_INSTANCE_ID NUMBER,
16 INVENTORY_ITEM_ID NUMBER,
17 MR_EFFECTIVITY_ID NUMBER,
18 UC_HEADER_ID NUMBER );
19 /* Define Table Type for Affected Item Instances */
20 TYPE MR_ITEM_INSTANCE_TBL_TYPE
21 IS
22 TABLE OF MR_ITEM_INSTANCE_REC_TYPE INDEX BY BINARY_INTEGER;
23 TYPE MR_HEADER_REC_TYPE
24 IS
25 RECORD
26 (
27 MR_HEADER_ID NUMBER,
28 MR_RELATIONSHIP_ID NUMBER,
29 TITLE AHL_MR_HEADERS_B.TITLE%TYPE,
30 REVISION AHL_MR_HEADERS_B.REVISION%TYPE,
31 VERSION_NUMBER AHL_MR_HEADERS_B.VERSION_NUMBER%TYPE,
32 MR_STATUS AHL_MR_HEADERS_B.MR_STATUS_CODE%TYPE,
33 REPETITIVE_FLAG AHL_MR_HEADERS_B.REPETITIVE_FLAG%TYPE,
34 IMPLEMENT_STATUS AHL_MR_HEADERS_B.IMPLEMENT_STATUS_CODE%TYPE,
35 DESCRIPTION AHL_MR_HEADERS_TL.DESCRIPTION%TYPE,
36 STATUS FND_LOOKUP_VALUES.MEANING%TYPE,
37 EFFECTIVE_FROM DATE,
38 EFFECTIVE_TO DATE,
39 MR_SEQUENCE NUMBER,
40 RELATIONSHIP VARCHAR2(30));
41 TYPE MR_CHAIN_LOOP_TBL_TYPE
42 IS
43 TABLE OF MR_HEADER_REC_TYPE INDEX BY BINARY_INTEGER;
44 TYPE MR_RELNS_REC_TYPE
45 IS
46 RECORD
47 (
48 MR_RELATIONSHIP_ID NUMBER,
49 MR_HEADER_ID NUMBER,
50 MR_SEQUENCE NUMBER,
51 RELATIONSHIP_CODE VARCHAR2(30));
52 TYPE MR_RELNS_TBL_TYPE
53 IS
54 TABLE OF MR_RELNS_REC_TYPE;
55 TYPE MR_HEADER_IDS_TBL_TYPE
56 IS
57 TABLE OF NUMBER INDEX BY BINARY_INTEGER;
58 TYPE ERR_MSG_DATA
59 IS
60 TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
61
62 PROCEDURE VALIDATE_LOOP_CHAIN
63 (
64 p_api_version IN NUMBER,
65 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
66 p_commit IN VARCHAR2 := FND_API.G_FALSE,
67 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
68 p_page_mode IN VARCHAR2 := 'CREATE',
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY ERR_MSG_DATA,
72 x_mr_header_id OUT NOCOPY MR_HEADER_IDS_TBL_TYPE,
73 P_MR_RELNS in MR_RELNS_TBL_TYPE );
74 FUNCTION get_valid_start_mr_rel
75 (
76 p_mr_header_id IN NUMBER,
77 p_start_mr_header_id IN NUMBER,
78 p_start_mr_rel_id IN NUMBER,
79 p_start_mr_title IN VARCHAR2,
80 p_effective_from IN DATE,
81 p_effective_to IN DATE,
82 p_rel_code IN VARCHAR2
83 ) RETURN NUMBER;
84 PROCEDURE get_start_mr_relns
85 (
86 p_mr_header_id IN NUMBER,
87 p_rel_code IN VARCHAR2,
88 x_start_mr_relns OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 );
93 PROCEDURE GET_CM_AFFECTED_ITEMS
94 (
95 p_api_version IN NUMBER,
96 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
97 p_commit IN VARCHAR2 := FND_API.G_FALSE,
98 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2,
102 x_mr_item_instances OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE,
103 p_cm_mr_chain_loop IN MR_CHAIN_LOOP_TBL_TYPE);
104 PROCEDURE VIEW_MR_LOOP_CHAIN
105 (
106 p_api_version IN NUMBER,
107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
108 p_commit IN VARCHAR2 := FND_API.G_FALSE,
109 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2,
113 p_start_mr_reln_id IN NUMBER,
114 p_relationship IN VARCHAR2,
115 p_mr_header_id IN NUMBER,
116 p_ue_id IN NUMBER,
117 p_calling_module IN VARCHAR2,
118 p_item_instance_id IN NUMBER,
119 x_mr_item_instances OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE,
120 x_cm_mr_chain_loop OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE
121 );
122 PROCEDURE get_valid_loop_chain_relns(
123 p_mr_header_id IN NUMBER,
124 p_effective_from IN DATE,
125 p_effective_to in date,
126 p_rel_code IN VARCHAR2,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 x_start_mr_relns out NOCOPY mr_chain_loop_tbl_type);
131
132 function IS_MR_IN_LOOP_OR_CHAIN (P_MR_HEADER_ID in number ) return varchar2;
133 function IS_MR_IN_LOOP(P_MR_HEADER_ID in number ) return varchar2;
134 function IS_MR_IN_CHAIN(P_MR_HEADER_ID in number ) RETURN VARCHAR2;
135
136 END AHL_MR_LOOP_CHAIN_RELNS_PVT;