-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathprovider_setup.sql
95 lines (89 loc) · 7.4 KB
/
provider_setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
-- DROP APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE;
CREATE APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE;
CREATE SCHEMA SHARE_ICEBERGER_HELPER_PACKAGE.TABLES;
CREATE SCHEMA SHARE_ICEBERGER_HELPER_PACKAGE.CODE;
CREATE OR REPLACE STAGE SHARE_ICEBERGER_HELPER_PACKAGE.CODE.AppCodeStage
file_format = (type = 'CSV' field_delimiter = '|' skip_header = 1);
-- upload code files to stage
-- example test table
CREATE OR REPLACE TABLE SHARE_ICEBERGER_HELPER_PACKAGE.TABLES.DATA_TBL (
ID INTEGER,
BUSINESSDATE VARCHAR,
FAMILYNAME VARCHAR,
MAINACCOUNTNAME VARCHAR,
NETCONTRACTAMT NUMBER,
REVENUE NUMBER,
VALIDATED VARCHAR,
TRXN INTEGER
);
INSERT INTO SHARE_ICEBERGER_HELPER_PACKAGE.TABLES.DATA_TBL VALUES
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',1),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',2),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',3),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',4),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',5),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',6),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',7),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',8),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',9),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',10),
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',11),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',12),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',13),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',14),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',15),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',16),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',17),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',18),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',19),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',20),
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',21),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',22),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',23),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',24),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',25),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',26),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',27),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',28),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',29),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',30),
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',31),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',32),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',33),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',34),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',35),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',36),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',37),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',38),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',39),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',40),
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',41),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',42),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',43),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',44),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',45),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',46),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',47),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',48),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',49),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',50),
('902','6/15/22','BOBBYS SCUBA SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'VALIDATION NEEDED',51),
('505','6/15/22','DANNYS CIGAR SHOP','SMOKE STACK UNLIMITED INC.',10,10,'VALIDATION NEEDED',52),
('5101','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'VALIDATION NEEDED',53),
('5','6/15/22','TEAS FROM TIMMY CO','WORLDWIDE LEAF HARVESTERS, LLC.',10,10,'VALIDATION NEEDED',54),
('901','6/15/22','AJITA’S KITCHENWARE','KITCHEN REFURB CONSTRUCTION, INC.',10,10,'VALIDATION NEEDED',55),
('50','6/15/22','YEEHAW A-LA RICH','BRONCO DETAILERS INTERNATIONAL, LLC.',10,10,'NO VALIDATION NEEDED',56),
('2950','6/15/22','ROBS CAMERAS','PHOTO ENTHUSIAST OF THE PNW, LLC.',10,10,'NO VALIDATION NEEDED',57),
('164','6/15/22','BOBBYS SCUBA & SURF SHACK','UNDERWATER ENTERPRISES, LLC.',10,10,'NO VALIDATION NEEDED',58),
('161','6/15/22','HOT DOGS OF THE WORLD','BEEFBITS OF AWEFUL, INC.',10,10,'NO VALIDATION NEEDED',59),
('226','6/15/22','HARTLANDS HAIR PRODUCTS','COIF CONNOISSEURS INC.',10,10,'NO VALIDATION NEEDED',60);
ALTER TABLE SHARE_ICEBERGER_HELPER_PACKAGE.TABLES.DATA_TBL SET CHANGE_TRACKING=TRUE;
GRANT USAGE ON SCHEMA SHARE_ICEBERGER_HELPER_PACKAGE.TABLES TO SHARE IN APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE;
GRANT SELECT ON TABLE SHARE_ICEBERGER_HELPER_PACKAGE.TABLES.DATA_TBL TO SHARE IN APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE;
ALTER APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE
ADD VERSION V1
USING '@SHARE_ICEBERGER_HELPER_PACKAGE.CODE.AppCodeStage';
-- test the app locally
CREATE APPLICATION SHARE_ICEBERGE_HELPER
FROM APPLICATION PACKAGE SHARE_ICEBERGER_HELPER_PACKAGE
USING VERSION V1;