· 9 years ago · Nov 21, 2016, 09:36 AM
1// Modification History
2// Date Who SDR What
3// 25oct16 nb Initial Version
4
5
6//TODO
7//Are all incoming records INSERT functions in Pronto?
8//have a copy of exports in processed
9
10//DONE
11//Go through all files that are *.csv and NOT ack*.csv
12//GUID for the first line and true to send something back
13//1 record is exported, not 3
14//If new record from biztalk creates an insert, DO NOT EXPORT
15//Change to export and import
16
17
18//Setup
19//1. Create dictionary triggers that track the deb-master, name-and-address-master, and system-table tables. They should call the salesforce-change procedure below
20//2. Setup module function codes for exporting the data
21//3. Setup the SF_EXPORTDIR environment variable
22//4. Create the template account. Dont forget to set deb-status = "N" and accountcode to be "Z_SF_TEMP"
23//5. Create the z-salesforce-change-log table in the dictionary according to commented out schema code below
24//6. Create the ZSF system table entry in the YY table for a counter for the export files
25//7. Create the salesforce-data table in the dictionary according to the commented out schema code below
26
27//object z-salesforce-change-log
28// type is memory
29// record is
30// scl-type pic X //'D'eb-master, 'N'ame-and-address-master, 'S'ystem-table
31// scl-date type date
32// scl-time type time
33// scl-processed type boolean
34// scl-key-1 like accountcode
35// scl-key-2 like sys-tbl-code
36// scl-change-type pic X //'I'nsert, 'U'pdate, 'D'elete
37// endrecord
38// key is scl-type scl-date scl-time unique
39
40// object z-salesforce-data
41// type is memory
42// record is
43// sd-accountcode pic X(10)
44// sd-salesforce-account-id pic X(20)
45// sd-salesforce-user-id pic X(20)
46// endrecord
47// key is sd-accountcode unique
48
49link 'clib/clibtax'
50
51#include "../include/bmsdef.spl"
52#include "../include/i8trigtags.spl"
53
54object tmp-customer-object
55 type is csv
56 separator is "|"
57 record is
58 sd-salesforce-account-id //AccountID
59 shortname //AccountName
60 accountcode //ProntoID
61 sd-salesforce-user-id //AccountOwner
62 tci-account-source pic X //Not stored in Pronto
63 na-street //BillingStreet
64 na-country //BillingCity
65 na-address-6 //BillingState
66 postcode //BillingPostalCode
67 na-address-7 //BillingCountry
68 na-fax-no //Fax
69 dr-marketing-flag //Parent
70 na-phone //Phone
71 dr-cust-type //CustomerType
72 terms-disc //PaymentType
73 tci-first-name pic X //Not stored in Pronto
74 tci-last-name pic X //Not stored in Pronto
75 territory //TerritoryCode
76 tci-territory-description like sys-description //TerritoryDescription
77 tci-region-code like sys-tbl-code //RegionCode
78 tci-region-description like sys-description //RegionDescription
79 endrecord
80
81object tmp-acknowledge
82 type is csv
83 record is
84 ta-data pic X(256)
85 endrecord
86
87
88field
89 ws-template-deb-status like deb-status
90 ws-template-dr-clearflag like dr-clearflag
91 ws-template-price-code like price-code
92 ws-template-warehouse like warehouse
93 ws-template-rep-code like rep-code
94 ws-template-territory like territory
95 ws-template-na-tax-group like na-tax-group
96 ws-template-dr-industry-code like dr-industry-code
97 ws-template-dr-cust-type like dr-cust-type
98 ws-template-dr-part-shipment-allowed like dr-part-shipment-allowed
99 ws-template-dr-order-priority like dr-order-priority
100 ws-template-dr-price-disc-by-bill-to like dr-price-disc-by-bill-to
101 ws-template-dr-ageing-code like dr-ageing-code
102 ws-report-started type boolean
103 ws-error-message pic X(256)
104
105procedure library-init
106 get system-control
107 first
108endprocedure
109
110
111procedure main export
112
113 get system-control
114 first
115
116 get deb-master
117 on index accountcode
118 key is "S_SF_TEMP"
119 on error
120 message "The S_SF_TEMP customer template account does not exist. Please create it."
121 else
122 set ws-template-deb-status = deb-status
123 set ws-template-dr-clearflag = dr-clearflag
124 set ws-template-price-code = price-code
125 set ws-template-warehouse = warehouse
126 set ws-template-rep-code = rep-code
127 set ws-template-territory = territory
128 get name-and-address-master
129 on index accountcode na-type
130 key is "S_SF_TEMP" "C"
131 on error
132 set ws-template-na-tax-group = spaces
133 else
134 set ws-template-na-tax-group = na-tax-group
135 endon
136 set ws-template-dr-industry-code = dr-industry-code
137 set ws-template-dr-cust-type = dr-cust-type
138 set ws-template-dr-part-shipment-allowed = dr-part-shipment-allowed
139 set ws-template-dr-order-priority = dr-order-priority
140 set ws-template-dr-price-disc-by-bill-to = dr-price-disc-by-bill-to
141 set ws-template-dr-ageing-code = dr-ageing-code
142 endon
143
144 command 'sh'
145 parameter "-c" concat("mkdir $SF_EXPORTDIR > /dev/null 2>&1")
146 command 'sh'
147 parameter "-c" concat("mkdir $SF_EXPORTDIR/export > /dev/null 2>&1")
148 command 'sh'
149 parameter "-c" concat("mkdir $SF_EXPORTDIR/export/processed > /dev/null 2>&1")
150 command 'sh'
151 parameter "-c" concat("mkdir $SF_EXPORTDIR/import > /dev/null 2>&1")
152
153
154 if get-param(1) = 'export' //Export Customer
155 do export-customer
156 elseif get-param(1) = "import" //Import Customer
157 do import-customer
158 endif
159
160endprocedure
161
162
163procedure get-counter
164 returning lr-counter like sys-money-value
165
166 get system-table
167 on index sys-tbl-type sys-tbl-code
168 key is "YY" "ZSF"
169 on error
170 message "Please initialize the YY/ZSF system table entry with 0"
171 set lr-counter = 0
172 else
173 serial system-table sys-money-value
174 on index sys-tbl-type sys-tbl-code
175 key is "YY" "ZSF"
176 set lr-counter = sys-money-value
177 endon
178
179endprocedure
180
181procedure import-customer export
182 local field
183 lf-ic-filename pic X(256)
184 lf-ack-filename like lf-ic-filename
185 lf-ok type boolean
186 lf-first-record type boolean
187 lf-session-id pic X(256)
188 lf-counter like sys-money-value
189 lf-dir pic X(300)
190 lf-search-pattern pic X(5)
191 lf-file-name pic X(300)
192
193 set lf-dir = concat(get-env("SF_EXPORTDIR"),"/import/")
194 set lf-search-pattern = '*.csv' // Find Report files whose names contain
195
196 if start-dir-search(lf-dir, lf-search-pattern)
197 set lf-dir-ok = TRUE
198 else
199 set lf-dir-ok = FALSE
200 endif
201 while lf-dir-ok
202 set lf-file-name = next-dir-entry()
203 if lf-file-name = SPACES // Search exhausted - No more files
204 elseif pattern(lf-file-name,"ack") = 1 //Dont pick up "ack"nowledge files
205 else
206 set lf-ic-filename = concat(get-env("SF_EXPORTDIR"),"/import/",lf-file-name)
207
208 open tmp-customer-object
209 file is lf-ic-filename
210
211 set lf-first-record = true
212
213 extract tmp-customer-object all
214 detail
215 //The first record contains only the session id, which is in the first column
216 if lf-first-record = true
217 set lf-session-id = sd-salesforce-account-id
218 set lf-first-record = false
219 continue
220 endif
221 do validate-lines
222 returning lf-ok
223 if lf-ok
224 open deb-master no-triggers
225 //The tables we are using already have the correct fields defined
226 insert deb-master
227 on error
228 endon
229
230 open z-salesforce-data no-triggers
231 set sd-accountcode = accountcode
232 insert z-salesforce-data
233 on error
234 endon
235
236 open name-and-address-master no-triggers
237 insert name-and-address-master
238 on error
239 endon
240 endif
241 endextract
242
243 do get-counter
244 returning lf-counter
245 set lf-ack-filename = concat(get-env("SF_EXPORTDIR"),"/import/ack-",str(lf-counter),".csv.wip")
246
247 open tmp-acknowledge create
248 file is lf-st-filename
249
250 set ta-data = lf-session-id
251 insert tmp-acknowledge
252 on error
253 endon
254 set ta-data = "true"
255 insert tmp-acknowledge
256 on error
257 endon
258
259 command 'sh'
260 parameter "-c" concat("cd $SF_EXPORTDIR/import; mv ack-",str(lf-counter),".csv.wip ack-",str(lf-counter),".csv")
261
262 endif
263 end-while
264
265 if ws-report-started = true
266 report finished
267 endif
268endprocedure
269
270procedure salesforce-change-dm export
271 parameters are
272 lp-old.* like deb-master.*
273 lp-new.* like deb-master.*
274 lp-trigger-type pic 9(4)
275 local field
276 lf-change-type pic X
277
278 if pattern(lp-new.accountcode,"SF_") = 1
279 if lp-trigger-type = TRIGGER_AFTER_INSERT
280 set lf-change-type = "I"
281 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
282 set lf-change-type = "U"
283 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
284 set lf-change-type = "D"
285 endif
286 do salesforce-change
287 parameters are "D" //deb-master
288 lp-new.accountcode
289 spaces
290 lf-change-type
291 endif
292endprocedure
293
294procedure salesforce-change-naam export
295 parameters are
296 lp-old.* like name-and-address-master.*
297 lp-new.* like name-and-address-master.*
298 lp-trigger-type pic 9(4)
299 local field
300 lf-change-type pic X
301
302 if pattern(lp-new.accountcode,"SF_") = 1 or
303 pattern(lp-old.accountcode,"SF_") = 1
304 if lp-trigger-type = TRIGGER_AFTER_INSERT
305 set lf-change-type = "I"
306 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
307 set lf-change-type = "U"
308 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
309 set lf-change-type = "D"
310 endif
311 do salesforce-change
312 parameters are "N" //name-and-address-master
313 lp-new.accountcode
314 lp-new.na-type
315 lf-change-type
316 endif
317
318endprocedure
319
320procedure salesforce-change-st export
321 parameters are
322 lp-old.* like system-table.*
323 lp-new.* like system-table.*
324 lp-trigger-type pic 9(4)
325 local field
326 lf-change-type pic X
327
328 if lp-trigger-type = TRIGGER_AFTER_INSERT
329 set lf-change-type = "I"
330 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
331 set lf-change-type = "U"
332 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
333 set lf-change-type = "D"
334 endif
335 do salesforce-change
336 parameters are "S" //system-table
337 lp-new.sys-tbl-code
338 lp-new.sys-tbl-type
339 lf-change-type
340
341endprocedure
342
343procedure salesforce-change export
344 parameters are lp-type like scl-type
345 lp-key-1 like scl-key-1
346 lp-key-2 like scl-key-2
347 lp-change-type like scl-change-type
348
349 set scl-type = lp-type
350 set scl-date = today()
351 set scl-time = tod()
352 set scl-processed = false
353 set scl-key-1 = lp-key-1
354 set scl-key-2 = lp-key-2
355 set scl-change-type = lp-change-type
356 insert z-salesforce-change-log
357 on error
358 endon
359
360endprocedure
361
362
363procedure export-deb-master
364 local field
365 lf-filename pic X(256)
366 lf-counter like sys-money-value
367
368 do get-counter
369 returning lf-counter
370 set lf-filename = concat(get-env("SF_EXPORTDIR"),"/export/pronto-sf-export-",str(lf-counter),".csv.wip")
371
372 open tmp-deb-master create permanent
373 file is lf-filename
374
375 extract z-salesforce-change-log all
376 on index scl-type scl-date scl-time
377 where scl-processed = false
378 detail
379 get deb-master
380 on index accountcode
381 key is scl-key-1
382 on error
383 else
384 endon
385 get name-and-address-master
386 on index accountcode na-type
387 key is scl-key-1 "C"
388 on error
389 else
390 endon
391 get z-salesforce-data
392 on index sd-accountcode
393 key is scl-key-1
394 on error
395 else
396 endon
397 insert tmp-customer-object
398 on error
399 else
400 set scl-processed = true
401 update z-salesforce-change-log
402 on error
403 endon
404 endon
405 endextract
406
407 command 'sh'
408 parameter "-c" concat("cd $SF_EXPORTDIR/export; mv pronto-sf-export-",str(lf-counter),".csv.wip pronto-sf-export-",str(lf-counter),".csv")
409 command 'sh'
410 parameter "-c" concat("cd $SF_EXPORTDIR/export; cp pronto-sf-export-",str(lf-counter),".csv processed/pronto-sf-export-",str(lf-counter),".csv")
411
412endprocedure
413
414procedure validate-lines
415 returning lr-ok type boolean
416
417 do validate-record
418 returning lr-ok
419
420 if lr-ok = false
421 //ws-error-message has the current error
422 if ws-report-started = false
423 report "Salesforce - Customer Import"
424 width is 200
425 endif
426 print ws-error-message col 1
427 endif
428
429endprocedure
430
431procedure validate-record
432 returning lr-ok type boolean
433 local field
434 lf-tax-group-ok type boolean
435 lf-tax-group-error-message pic X(256)
436 lf-tax-group-error-title pic X(256)
437
438 set lr-ok = true
439 get deb-master
440 on index accountcode
441 key is accountcode
442 on error
443 //Good. We want a unique new accountcode
444 else //Account exists
445 set ws-error-message = "Account code already exists"
446 set lr-ok = false
447 exit
448 endon
449
450// if deb-status = 'I' and credit-limit between '0' and '9'
451 if deb-status != spaces
452// set ws-error-message = 'WARNING - Can only use limits "A" - "Z" with "Insured Debt" '
453 set deb-status = ws-template-deb-status
454 set ws-error-message = 'deb-status should be blank. Copied from template account.'
455 //set lr-ok = false
456 //exit
457 endif
458 if dr-clearflag != 'O'
459 set dr-clearflag = ws-template-dr-clearflag
460 set ws-error-message = "Clear flag should be O. Copied from template account."
461 //set lr-ok = false
462 //exit
463 endif
464 if warehouse = spaces
465 set warehouse = ws-template-warehouse
466 set ws-error-message = "Warehouse can not be spaces. Copied from template account."
467 //set lr-ok = false
468 //exit
469 endif
470 if warehouse != spaces
471 get system-table
472 on index sys-tbl-type sys-tbl-code
473 key is 'WH' warehouse
474 on error
475 set ws-error-message = "Warehouse not found"
476 set lr-ok = false
477 exit
478 endon
479 endif
480 if rep-code = spaces
481 set rep-code = ws-template-rep-code
482 set ws-error-message = "Rep code can not be spaces. Copied from template account."
483 //set lr-ok = false
484 //exit
485 endif
486 if rep-code != spaces
487 get rep-master
488 key is rep-code
489 on error
490 set ws-error-message = 'Rep code not valid'
491 set lr-ok = false
492 exit
493 endon
494 endif
495 if territory = spaces
496 set territory = ws-template-territory
497 set ws-error-message = "Territory can not be spaces. Copied from template account."
498 //set lr-ok = false
499 //exit
500 endif
501 if territory != spaces
502 get system-table
503 on index sys-tbl-type sys-tbl-code
504 key is 'TC' territory
505 on error
506 set ws-error-message = "Territory not valid"
507 set lr-ok = false
508 exit
509 endon
510 endif
511 if na-tax-group = SPACES
512 set na-tax-group = ws-template-na-tax-group
513 set ws-error-message = "Tax group can not be spaces. Copied from template account."
514 //set lr-ok = false
515 //exit
516 endif
517 if na-tax-group != SPACES
518 do clibtax-validate-tax-group
519 parameters
520 na-tax-group
521 returning
522 lf-tax-group-ok
523 lf-tax-group-error-message
524 lf-tax-group-error-title
525 if not lf-tax-group-ok
526 set ws-error-message = lf-tax-group-error-message
527 set lr-ok = false
528 exit
529 endif
530 endif
531 if dr-industry-code = spaces
532 set dr-industry-code = ws-template-dr-industry-code
533 set ws-error-message = "Industry Code can not be spaces. Copied from template account."
534 //set lr-ok = false
535 //exit
536 endif
537 if dr-industry-code != spaces
538 get system-table
539 on index sys-tbl-type sys-tbl-code
540 key is 'CI' dr-industry-code
541 on error
542 set ws-error-message = "Industry code not valid"
543 set lr-ok = false
544 exit
545 endon
546 endif
547 if dr-cust-type = spaces
548 set dr-cust-type = ws-template-dr-cust-type
549 set ws-error-message = "Cust type can not be spaces. Copied from template account."
550 //set lr-ok = false
551 //exit
552 endif
553 if dr-cust-type != spaces
554 get system-table
555 on index sys-tbl-type sys-tbl-code
556 key is 'CT' dr-cust-type
557 on error
558 set ws-error-message = "Cust type not valid"
559 set lr-ok = false
560 exit
561 endon
562 endif
563 if dr-part-shipment-allowed not in {'Y' 'N'}
564 set dr-part-shipment-allowed = ws-template-dr-part-shipment-allowed
565 set ws-error-message = "Part shipment allowed should be 'Y' or 'N'. Copied from template account."
566 //set lr-ok = false
567 //exit
568 endif
569 if dr-order-priority > 9
570 set dr-order-priority = ws-template-dr-order-priority
571 set ws-error-message = "Order Priority should be between 0-9. Copied from template account."
572 //set lr-ok = false
573 //exit
574 endif
575 if dr-price-disc-by-bill-to not in {'Y' 'N' ' '}
576 set dr-price-disc-by-bill-to = ws-template-dr-price-disc-by-bill-to
577 set ws-error-message = "Price disc by bill-to should be 'Y' , 'N' or blank. Copied from template account."
578 //set lr-ok = false
579 //exit
580 endif
581 if dr-ageing-code = spaces
582 set dr-ageing-code = ws-template-dr-ageing-code
583 set ws-error-message = "Ageing code can not be spaces. Copied from template account."
584 //set lr-ok = false
585 //exit
586 endif
587 if dr-ageing-code != spaces
588 get system-table
589 on index sys-tbl-type sys-tbl-code
590 key is 'DK' dr-ageing-code
591 on error
592 set ws-error-message = "Ageing code not valid"
593 set lr-ok = false
594 exit
595 endon
596 endif
597
598 //Left here in case of future need...
599 // ////////////////// Other field validations///////////////////
600 // //z-terms-disc
601 // if terms-disc != SPACES
602 // get system-table
603 // on index sys-tbl-type sys-tbl-code
604 // key is "DD" terms-disc
605 // on error
606 // set ws-error-code = "0020"
607 // set ws-error-message = "Invalid terms code"
608 // //message "Invalid terms code"
609 // set lr-ok = false
610 // //exit(1)
611 // endon
612 // endif
613 // //z-dr-marketing-flag
614 // if dr-marketing-flag != SPACES
615 // get system-table
616 // key is 'CM' dr-marketing-flag
617 // on error
618 // set ws-error-code = "0021"
619 // set ws-error-message = 'Invalid marketing flag'
620 // //message 'Invalid marketing flag'
621 // set lr-ok = false
622 // //exit(1)
623 // endon
624 // endif
625 // //z-dr-company-mask
626 // if dr-company-mask != SPACES
627 // get system-table
628 // key is 'BC' dr-company-mask
629 // on error
630 // set ws-error-code = "0022"
631 // set ws-error-message = 'Invalid company flag'
632 // //message 'Invalid company flag'
633 // set lr-ok = false
634 // //exit(1)
635 // endon
636 // endif
637 // //z-dr-credit-letter-status
638 // if dr-credit-letter-status not in {'Y' 'N' 'X' ' '}
639 // set ws-error-code = "0023"
640 // set ws-error-message = "Cust Sign Ind. should be 'Y' , 'N' or 'X'"
641 // //message "Cust Sign Ind. should be 'Y' , 'N' or 'X'"
642 // set lr-ok = false
643 // //exit(1)
644 // endif
645 // //z-dr-pricing-category
646 // if dr-pricing-category != SPACES
647 // get system-table
648 // key is 'PE' dr-pricing-category
649 // on error
650 // set ws-error-code = "0024"
651 // set ws-error-message = 'Invalid pricing category '
652 // //message 'Invalid pricing category '
653 // set lr-ok = false
654 // //exit(1)
655 // endon
656 // endif
657 // //z-dr-curr-code
658 // if dr-curr-code != SPACES
659 // // get system-iso4217-currency-codes
660 // // on index si4217cc-currency-code
661 // // key is dr-curr-code
662 // get system-table
663 // key is 'CU' dr-curr-code
664 // on error
665 // set ws-error-code = "0025"
666 // set ws-error-message = 'Invalid currency code'
667 // //message 'Invalid currency code'
668 // set lr-ok = false
669 // //exit(1)
670 // endon
671 // endif
672 // //z-dr-delivery-route-code
673 // if dr-delivery-route-code != SPACES
674 // get system-carrier
675 // key is dr-delivery-route-code
676 // on error
677 // set ws-error-code = "0026"
678 // set ws-error-message = 'Invalid carrier code '
679 // //message 'Invalid carrier code '
680 // set lr-ok = false
681 // //exit(1)
682 // endon
683 // endif
684 // //z-dr-delivery-sequence-no
685 // if dr-delivery-sequence-no != 0
686 // if dr-delivery-route-code = SPACES
687 // set ws-error-code = "0027"
688 // set ws-error-message = 'Carrier code is blank, can not set route sequence no'
689 // //message 'Carrier code is blank, can not set route sequence no'
690 // set lr-ok = false
691 // //exit(1)
692 // else
693 // get system-carrier
694 // key is dr-delivery-route-code
695 // on error
696 // else
697 // if carrier-charge-method in {'R' 'X' 'S'}
698 // get system-carrier-routes
699 // on index car-route-code carrier-code
700 // key is str(z-dr-delivery-sequence-no) dr-delivery-route-code
701 // on error
702 // set ws-error-code = "0028"
703 // set ws-error-message = 'Invalid route sequence code for this carrier'
704 // //message 'Invalid route sequence code for this carrier'
705 // set lr-ok = false
706 // //exit(1)
707 // endon
708 // endif
709 // endon
710 // endif
711 // endif
712 // //z-dr-credit-id-no
713 // if dr-credit-id-no != SPACES
714 // get system-table
715 // key is 'DE' dr-credit-id-no
716 // on error
717 // set ws-error-code = "0029"
718 // set ws-error-message = 'Invalid customer extra code'
719 // //message 'Invalid customer extra code '
720 // set lr-ok = false
721 // //exit(1)
722 // endon
723 // endif
724 // //z-dr-mail-control-code
725 // if dr-mail-control-code not in {' ' 'N' 'I' 'J' 'S' 'D' 'L' '1' '2' '3' '4' '5' '6' '7' '8' '9' 'C' 'F' 'R' 'B'}
726 // set ws-error-code = "0030"
727 // set ws-error-message = "Mailing control should be ' ' ,'N', 'I', 'J', 'S' ,'D' ,'L', '1' to '9', 'C', 'F', 'R' or 'B'"
728 // //message "Mailing control should be ' ' ,'N', 'I', 'J', 'S' ,'D' ,'L', '1' to '9', 'C', 'F', 'R' or 'B'"
729 // set lr-ok = false
730 // //exit(1)
731 // endif
732 // //z-dr-industry-sub-group
733 // if dr-industry-sub-group != SPACES
734 // get system-table
735 // key is 'CG' dr-industry-sub-group
736 // on error
737 // set ws-error-code = "0031"
738 // set ws-error-message = 'Invalid industry sub group '
739 // //message 'Invalid industry sub group '
740 // set lr-ok = false
741 // //exit(1)
742 // endon
743 // endif
744endprocedure