1 PACKAGE EGO_STYLE_SKU_ITEM_PVT AS
2 /* $Header: EGOSITMS.pls 120.12 2007/10/09 21:35:36 mantyaku noship $ */
3
4 G_MISS_NUM CONSTANT NUMBER := 9.99E125;
5 G_MISS_CHAR CONSTANT VARCHAR2(1) := CHR(0);
6 G_MISS_DATE CONSTANT DATE := TO_DATE('1','j');
7 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE; -- 'F'
8 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE; -- 'T'
9
10 FUNCTION IsStyle_Item_Exist_For_ICC
11 (
12 p_item_catalog_group_id IN NUMBER
13 ) RETURN VARCHAR2;
14
15 FUNCTION IsSKU_Item_Exist_For_ICC
16 (
17 p_item_catalog_group_id IN NUMBER
18 ) RETURN VARCHAR2;
19
20 PROCEDURE Process_Items
21 (
22 p_set_process_id IN NUMBER
23 ,p_Process_Flag IN NUMBER
24 ,p_commit IN VARCHAR2 DEFAULT G_FALSE
25 ,p_Transaction_Type IN VARCHAR2 DEFAULT NULL
26 ,p_Template_Id IN NUMBER DEFAULT NULL
27 ,p_copy_inventory_item_Id IN NUMBER DEFAULT NULL
28 ,p_copy_revision_Id IN NUMBER DEFAULT NULL
29 ,p_inventory_item_id IN NUMBER DEFAULT NULL
30 ,p_organization_id IN NUMBER DEFAULT NULL
31 ,p_description IN VARCHAR2 DEFAULT NULL
32 ,p_long_description IN VARCHAR2 DEFAULT NULL
33 ,p_primary_uom_code IN VARCHAR2 DEFAULT NULL
34 ,p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
35 ,p_item_type IN VARCHAR2 DEFAULT NULL
36 ,p_inventory_item_status_code IN VARCHAR2 DEFAULT NULL
37 ,p_allowed_units_lookup_code IN NUMBER DEFAULT NULL
38 ,p_item_catalog_group_id IN NUMBER DEFAULT NULL
39 ,p_bom_enabled_flag IN VARCHAR2 DEFAULT NULL
40 ,p_eng_item_flag IN VARCHAR2 DEFAULT NULL
41 ,p_weight_uom_code IN VARCHAR2 DEFAULT NULL
42 ,p_unit_weight IN NUMBER DEFAULT NULL
43 ,p_Item_Number IN VARCHAR2 DEFAULT NULL
44 ,p_Style_Item_Flag IN VARCHAR2 DEFAULT NULL
45 ,p_Style_Item_Id IN NUMBER DEFAULT NULL
46 ,p_Style_Item_Number IN VARCHAR2 DEFAULT NULL
47 ,p_Gdsn_Outbound_Enabled_Flag IN VARCHAR2 DEFAULT NULL
48 ,p_Trade_Item_Descriptor IN VARCHAR2 DEFAULT NULL
49 ) ;
50
51 PROCEDURE Process_Items
52 (
53 p_commit IN VARCHAR2 DEFAULT G_FALSE
54 ,p_Item_Intf_Data_Tab IN OUT NOCOPY EGO_ITEM_INTF_DATA_TAB
55 ,x_return_status OUT NOCOPY VARCHAR2
56 ,x_msg_data OUT NOCOPY VARCHAR2
57 ,x_msg_count OUT NOCOPY NUMBER
58 );
59
60 /*
61 * This API validates that the variant attribute combination for the SKU
62 * is unique. It also inserts the record if combination does not exists
63 * This API sets x_sku_exists as TRUE if combination already exists
64 * This API sets x_sku_exists as FALSE if combination is not found
65 * This API sets x_var_attrs_missing as TRUE if some variant attribute
66 * values are missing.
67 *
68 * This API returns 0 if no unexpected errors are there, else
69 * returns the SQLCODE
70 *
71 * This API assumes that INVENTORY_ITEM_ID will be present in the intf table
72 */
73 FUNCTION Validate_SKU_Variant_Usage( p_intf_row_id IN ROWID
74 , x_sku_exists OUT NOCOPY BOOLEAN
75 , x_var_attrs_missing OUT NOCOPY BOOLEAN
76 , x_err_text OUT NOCOPY VARCHAR2
77 )
78 RETURN INTEGER;
79
80 FUNCTION Default_Style_Variant_Attrs(p_inventory_item_id IN NUMBER,
81 p_item_catalog_group_id IN NUMBER,
82 x_err_text OUT NOCOPY VARCHAR2)
83 RETURN INTEGER;
84
85 /*
86 * This method returns FND_API.G_TRUE or FND_API.G_FALSE
87 * This method computes whether it is ok to have the new parent ICC
88 * wrt style functionality i.e. we should not allow a ICC that has
89 * different variant attributes than that are currently associated
90 * with the ICC, if ICC already has some styles created.
91 */
92 FUNCTION Is_Parent_ICC_Valid_For_Style(p_item_catalog_group_id NUMBER,
93 p_parent_catalog_group_id NUMBER)
94 RETURN VARCHAR2;
95
96 /*
97 * This method inserts a Fake row in the interface table.
98 */
99 PROCEDURE Insert_Fake_Row_For_Item( p_commit IN VARCHAR2 DEFAULT G_FALSE
100 ,p_batch_id IN NUMBER
101 ,p_inventory_item_id IN NUMBER
102 ,p_organization_id IN NUMBER
103 ,p_item_number IN VARCHAR2
104 ,p_style_item_flag IN VARCHAR2
105 ,p_style_item_id IN NUMBER
106 ,p_item_catalog_group_id IN NUMBER
107 ,x_return_status OUT NOCOPY VARCHAR2
108 ,x_msg_data OUT NOCOPY VARCHAR2);
109
110 /*
111 * This method inserts Role records for SKUs in the item people interface table.
112 */
113 PROCEDURE Propagate_Role_To_SKUs ( p_commit IN VARCHAR2 DEFAULT G_FALSE
114 ,p_batch_id IN NUMBER
115 ,p_style_item_id IN NUMBER
116 ,p_organization_id IN NUMBER
117 ,p_role_name IN VARCHAR2
118 ,p_grantee_type IN VARCHAR2
119 ,p_grantee_party_id IN NUMBER
120 ,p_end_date IN DATE
121 ,x_return_status OUT NOCOPY VARCHAR2
122 ,x_msg_data OUT NOCOPY VARCHAR2);
123
124 /*
125 * This method inserts Category assignment records for SKUs in the mtl categories interface table.
126 */
127 PROCEDURE Propagate_Category_To_SKUs ( p_commit IN VARCHAR2 DEFAULT G_FALSE
128 ,p_batch_id IN NUMBER
129 ,p_style_item_id IN NUMBER
130 ,p_organization_id IN NUMBER
131 ,p_category_set_id IN NUMBER
132 ,p_category_id IN NUMBER
133 ,x_return_status OUT NOCOPY VARCHAR2
134 ,x_msg_data OUT NOCOPY VARCHAR2);
135
136 END EGO_STYLE_SKU_ITEM_PVT;