· 9 years ago · Nov 21, 2016, 09:44 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 lf-dir-ok type boolean
193
194 set lf-dir = concat(get-env("SF_EXPORTDIR"),"/import/")
195 set lf-search-pattern = '*.csv' // Find Report files whose names contain
196
197 if start-dir-search(lf-dir, lf-search-pattern)
198 set lf-dir-ok = TRUE
199 else
200 set lf-dir-ok = FALSE
201 endif
202 while lf-dir-ok
203 set lf-file-name = next-dir-entry()
204 if lf-file-name = SPACES // Search exhausted - No more files
205 elseif pattern(lf-file-name,"ack") = 1 //Dont pick up "ack"nowledge files
206 else
207 set lf-ic-filename = concat(get-env("SF_EXPORTDIR"),"/import/",lf-file-name)
208
209 open tmp-customer-object
210 file is lf-ic-filename
211
212 set lf-first-record = true
213
214 extract tmp-customer-object all
215 detail
216 //The first record contains only the session id, which is in the first column
217 if lf-first-record = true
218 set lf-session-id = sd-salesforce-account-id
219 set lf-first-record = false
220 continue
221 endif
222 do validate-lines
223 returning lf-ok
224 if lf-ok
225 open deb-master no-triggers
226 //The tables we are using already have the correct fields defined
227 insert deb-master
228 on error
229 endon
230
231 open z-salesforce-data no-triggers
232 set sd-accountcode = accountcode
233 insert z-salesforce-data
234 on error
235 endon
236
237 open name-and-address-master no-triggers
238 insert name-and-address-master
239 on error
240 endon
241 endif
242 endextract
243
244 do get-counter
245 returning lf-counter
246 set lf-ack-filename = concat(get-env("SF_EXPORTDIR"),"/import/ack-",str(lf-counter),".csv.wip")
247
248 open tmp-acknowledge create
249 file is lf-ack-filename
250
251 set ta-data = lf-session-id
252 insert tmp-acknowledge
253 on error
254 endon
255 set ta-data = "true"
256 insert tmp-acknowledge
257 on error
258 endon
259
260 command 'sh'
261 parameter "-c" concat("cd $SF_EXPORTDIR/import; mv ack-",str(lf-counter),".csv.wip ack-",str(lf-counter),".csv")
262
263 endif
264 end-while
265
266 if ws-report-started = true
267 report finished
268 endif
269endprocedure
270
271procedure salesforce-change-dm export
272 parameters are
273 lp-old.* like deb-master.*
274 lp-new.* like deb-master.*
275 lp-trigger-type pic 9(4)
276 local field
277 lf-change-type pic X
278
279 if pattern(lp-new.accountcode,"SF_") = 1
280 if lp-trigger-type = TRIGGER_AFTER_INSERT
281 set lf-change-type = "I"
282 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
283 set lf-change-type = "U"
284 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
285 set lf-change-type = "D"
286 endif
287 do salesforce-change
288 parameters are "D" //deb-master
289 lp-new.accountcode
290 spaces
291 lf-change-type
292 endif
293endprocedure
294
295procedure salesforce-change-naam export
296 parameters are
297 lp-old.* like name-and-address-master.*
298 lp-new.* like name-and-address-master.*
299 lp-trigger-type pic 9(4)
300 local field
301 lf-change-type pic X
302
303 if pattern(lp-new.accountcode,"SF_") = 1 or
304 pattern(lp-old.accountcode,"SF_") = 1
305 if lp-trigger-type = TRIGGER_AFTER_INSERT
306 set lf-change-type = "I"
307 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
308 set lf-change-type = "U"
309 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
310 set lf-change-type = "D"
311 endif
312 do salesforce-change
313 parameters are "N" //name-and-address-master
314 lp-new.accountcode
315 lp-new.na-type
316 lf-change-type
317 endif
318
319endprocedure
320
321procedure salesforce-change-st export
322 parameters are
323 lp-old.* like system-table.*
324 lp-new.* like system-table.*
325 lp-trigger-type pic 9(4)
326 local field
327 lf-change-type pic X
328
329 if lp-trigger-type = TRIGGER_AFTER_INSERT
330 set lf-change-type = "I"
331 elseif lp-trigger-type = TRIGGER_AFTER_UPDATE
332 set lf-change-type = "U"
333 elseif lp-trigger-type = TRIGGER_AFTER_DELETE
334 set lf-change-type = "D"
335 endif
336 do salesforce-change
337 parameters are "S" //system-table
338 lp-new.sys-tbl-code
339 lp-new.sys-tbl-type
340 lf-change-type
341
342endprocedure
343
344procedure salesforce-change export
345 parameters are lp-type like scl-type
346 lp-key-1 like scl-key-1
347 lp-key-2 like scl-key-2
348 lp-change-type like scl-change-type
349
350 set scl-type = lp-type
351 set scl-date = today()
352 set scl-time = tod()
353 set scl-processed = false
354 set scl-key-1 = lp-key-1
355 set scl-key-2 = lp-key-2
356 set scl-change-type = lp-change-type
357 insert z-salesforce-change-log
358 on error
359 endon
360
361endprocedure
362
363
364procedure export-customer
365 local field
366 lf-filename pic X(256)
367 lf-counter like sys-money-value
368
369 do get-counter
370 returning lf-counter
371 set lf-filename = concat(get-env("SF_EXPORTDIR"),"/export/pronto-sf-export-",str(lf-counter),".csv.wip")
372
373 open tmp-customer-object create permanent
374 file is lf-filename
375
376 extract z-salesforce-change-log all
377 on index scl-type scl-date scl-time
378 where scl-processed = false
379 detail
380 get deb-master
381 on index accountcode
382 key is scl-key-1
383 on error
384 else
385 endon
386 get name-and-address-master
387 on index accountcode na-type
388 key is scl-key-1 "C"
389 on error
390 else
391 endon
392 get z-salesforce-data
393 on index sd-accountcode
394 key is scl-key-1
395 on error
396 else
397 endon
398 insert tmp-customer-object
399 on error
400 else
401 set scl-processed = true
402 update z-salesforce-change-log
403 on error
404 endon
405 endon
406 endextract
407
408 command 'sh'
409 parameter "-c" concat("cd $SF_EXPORTDIR/export; mv pronto-sf-export-",str(lf-counter),".csv.wip pronto-sf-export-",str(lf-counter),".csv")
410 command 'sh'
411 parameter "-c" concat("cd $SF_EXPORTDIR/export; cp pronto-sf-export-",str(lf-counter),".csv processed/pronto-sf-export-",str(lf-counter),".csv")
412
413endprocedure
414
415procedure validate-lines
416 returning lr-ok type boolean
417
418 do validate-record
419 returning lr-ok
420
421 if lr-ok = false
422 //ws-error-message has the current error
423 if ws-report-started = false
424 report "Salesforce - Customer Import"
425 width is 200
426 endif
427 print ws-error-message col 1
428 endif
429
430endprocedure
431
432procedure validate-record
433 returning lr-ok type boolean
434 local field
435 lf-tax-group-ok type boolean
436 lf-tax-group-error-message pic X(256)
437 lf-tax-group-error-title pic X(256)
438
439 set lr-ok = true
440 get deb-master
441 on index accountcode
442 key is accountcode
443 on error
444 //Good. We want a unique new accountcode
445 else //Account exists
446 set ws-error-message = "Account code already exists"
447 set lr-ok = false
448 exit
449 endon
450
451// if deb-status = 'I' and credit-limit between '0' and '9'
452 if deb-status != spaces
453// set ws-error-message = 'WARNING - Can only use limits "A" - "Z" with "Insured Debt" '
454 set deb-status = ws-template-deb-status
455 set ws-error-message = 'deb-status should be blank. Copied from template account.'
456 //set lr-ok = false
457 //exit
458 endif
459 if dr-clearflag != 'O'
460 set dr-clearflag = ws-template-dr-clearflag
461 set ws-error-message = "Clear flag should be O. Copied from template account."
462 //set lr-ok = false
463 //exit
464 endif
465 if warehouse = spaces
466 set warehouse = ws-template-warehouse
467 set ws-error-message = "Warehouse can not be spaces. Copied from template account."
468 //set lr-ok = false
469 //exit
470 endif
471 if warehouse != spaces
472 get system-table
473 on index sys-tbl-type sys-tbl-code
474 key is 'WH' warehouse
475 on error
476 set ws-error-message = "Warehouse not found"
477 set lr-ok = false
478 exit
479 endon
480 endif
481 if rep-code = spaces
482 set rep-code = ws-template-rep-code
483 set ws-error-message = "Rep code can not be spaces. Copied from template account."
484 //set lr-ok = false
485 //exit
486 endif
487 if rep-code != spaces
488 get rep-master
489 key is rep-code
490 on error
491 set ws-error-message = 'Rep code not valid'
492 set lr-ok = false
493 exit
494 endon
495 endif
496 if territory = spaces
497 set territory = ws-template-territory
498 set ws-error-message = "Territory can not be spaces. Copied from template account."
499 //set lr-ok = false
500 //exit
501 endif
502 if territory != spaces
503 get system-table
504 on index sys-tbl-type sys-tbl-code
505 key is 'TC' territory
506 on error
507 set ws-error-message = "Territory not valid"
508 set lr-ok = false
509 exit
510 endon
511 endif
512 if na-tax-group = SPACES
513 set na-tax-group = ws-template-na-tax-group
514 set ws-error-message = "Tax group can not be spaces. Copied from template account."
515 //set lr-ok = false
516 //exit
517 endif
518 if na-tax-group != SPACES
519 do clibtax-validate-tax-group
520 parameters
521 na-tax-group
522 returning
523 lf-tax-group-ok
524 lf-tax-group-error-message
525 lf-tax-group-error-title
526 if not lf-tax-group-ok
527 set ws-error-message = lf-tax-group-error-message
528 set lr-ok = false
529 exit
530 endif
531 endif
532 if dr-industry-code = spaces
533 set dr-industry-code = ws-template-dr-industry-code
534 set ws-error-message = "Industry Code can not be spaces. Copied from template account."
535 //set lr-ok = false
536 //exit
537 endif
538 if dr-industry-code != spaces
539 get system-table
540 on index sys-tbl-type sys-tbl-code
541 key is 'CI' dr-industry-code
542 on error
543 set ws-error-message = "Industry code not valid"
544 set lr-ok = false
545 exit
546 endon
547 endif
548 if dr-cust-type = spaces
549 set dr-cust-type = ws-template-dr-cust-type
550 set ws-error-message = "Cust type can not be spaces. Copied from template account."
551 //set lr-ok = false
552 //exit
553 endif
554 if dr-cust-type != spaces
555 get system-table
556 on index sys-tbl-type sys-tbl-code
557 key is 'CT' dr-cust-type
558 on error
559 set ws-error-message = "Cust type not valid"
560 set lr-ok = false
561 exit
562 endon
563 endif
564 if dr-part-shipment-allowed not in {'Y' 'N'}
565 set dr-part-shipment-allowed = ws-template-dr-part-shipment-allowed
566 set ws-error-message = "Part shipment allowed should be 'Y' or 'N'. Copied from template account."
567 //set lr-ok = false
568 //exit
569 endif
570 if dr-order-priority > 9
571 set dr-order-priority = ws-template-dr-order-priority
572 set ws-error-message = "Order Priority should be between 0-9. Copied from template account."
573 //set lr-ok = false
574 //exit
575 endif
576 if dr-price-disc-by-bill-to not in {'Y' 'N' ' '}
577 set dr-price-disc-by-bill-to = ws-template-dr-price-disc-by-bill-to
578 set ws-error-message = "Price disc by bill-to should be 'Y' , 'N' or blank. Copied from template account."
579 //set lr-ok = false
580 //exit
581 endif
582 if dr-ageing-code = spaces
583 set dr-ageing-code = ws-template-dr-ageing-code
584 set ws-error-message = "Ageing code can not be spaces. Copied from template account."
585 //set lr-ok = false
586 //exit
587 endif
588 if dr-ageing-code != spaces
589 get system-table
590 on index sys-tbl-type sys-tbl-code
591 key is 'DK' dr-ageing-code
592 on error
593 set ws-error-message = "Ageing code not valid"
594 set lr-ok = false
595 exit
596 endon
597 endif
598
599 //Left here in case of future need...
600 // ////////////////// Other field validations///////////////////
601 // //z-terms-disc
602 // if terms-disc != SPACES
603 // get system-table
604 // on index sys-tbl-type sys-tbl-code
605 // key is "DD" terms-disc
606 // on error
607 // set ws-error-code = "0020"
608 // set ws-error-message = "Invalid terms code"
609 // //message "Invalid terms code"
610 // set lr-ok = false
611 // //exit(1)
612 // endon
613 // endif
614 // //z-dr-marketing-flag
615 // if dr-marketing-flag != SPACES
616 // get system-table
617 // key is 'CM' dr-marketing-flag
618 // on error
619 // set ws-error-code = "0021"
620 // set ws-error-message = 'Invalid marketing flag'
621 // //message 'Invalid marketing flag'
622 // set lr-ok = false
623 // //exit(1)
624 // endon
625 // endif
626 // //z-dr-company-mask
627 // if dr-company-mask != SPACES
628 // get system-table
629 // key is 'BC' dr-company-mask
630 // on error
631 // set ws-error-code = "0022"
632 // set ws-error-message = 'Invalid company flag'
633 // //message 'Invalid company flag'
634 // set lr-ok = false
635 // //exit(1)
636 // endon
637 // endif
638 // //z-dr-credit-letter-status
639 // if dr-credit-letter-status not in {'Y' 'N' 'X' ' '}
640 // set ws-error-code = "0023"
641 // set ws-error-message = "Cust Sign Ind. should be 'Y' , 'N' or 'X'"
642 // //message "Cust Sign Ind. should be 'Y' , 'N' or 'X'"
643 // set lr-ok = false
644 // //exit(1)
645 // endif
646 // //z-dr-pricing-category
647 // if dr-pricing-category != SPACES
648 // get system-table
649 // key is 'PE' dr-pricing-category
650 // on error
651 // set ws-error-code = "0024"
652 // set ws-error-message = 'Invalid pricing category '
653 // //message 'Invalid pricing category '
654 // set lr-ok = false
655 // //exit(1)
656 // endon
657 // endif
658 // //z-dr-curr-code
659 // if dr-curr-code != SPACES
660 // // get system-iso4217-currency-codes
661 // // on index si4217cc-currency-code
662 // // key is dr-curr-code
663 // get system-table
664 // key is 'CU' dr-curr-code
665 // on error
666 // set ws-error-code = "0025"
667 // set ws-error-message = 'Invalid currency code'
668 // //message 'Invalid currency code'
669 // set lr-ok = false
670 // //exit(1)
671 // endon
672 // endif
673 // //z-dr-delivery-route-code
674 // if dr-delivery-route-code != SPACES
675 // get system-carrier
676 // key is dr-delivery-route-code
677 // on error
678 // set ws-error-code = "0026"
679 // set ws-error-message = 'Invalid carrier code '
680 // //message 'Invalid carrier code '
681 // set lr-ok = false
682 // //exit(1)
683 // endon
684 // endif
685 // //z-dr-delivery-sequence-no
686 // if dr-delivery-sequence-no != 0
687 // if dr-delivery-route-code = SPACES
688 // set ws-error-code = "0027"
689 // set ws-error-message = 'Carrier code is blank, can not set route sequence no'
690 // //message 'Carrier code is blank, can not set route sequence no'
691 // set lr-ok = false
692 // //exit(1)
693 // else
694 // get system-carrier
695 // key is dr-delivery-route-code
696 // on error
697 // else
698 // if carrier-charge-method in {'R' 'X' 'S'}
699 // get system-carrier-routes
700 // on index car-route-code carrier-code
701 // key is str(z-dr-delivery-sequence-no) dr-delivery-route-code
702 // on error
703 // set ws-error-code = "0028"
704 // set ws-error-message = 'Invalid route sequence code for this carrier'
705 // //message 'Invalid route sequence code for this carrier'
706 // set lr-ok = false
707 // //exit(1)
708 // endon
709 // endif
710 // endon
711 // endif
712 // endif
713 // //z-dr-credit-id-no
714 // if dr-credit-id-no != SPACES
715 // get system-table
716 // key is 'DE' dr-credit-id-no
717 // on error
718 // set ws-error-code = "0029"
719 // set ws-error-message = 'Invalid customer extra code'
720 // //message 'Invalid customer extra code '
721 // set lr-ok = false
722 // //exit(1)
723 // endon
724 // endif
725 // //z-dr-mail-control-code
726 // 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'}
727 // set ws-error-code = "0030"
728 // set ws-error-message = "Mailing control should be ' ' ,'N', 'I', 'J', 'S' ,'D' ,'L', '1' to '9', 'C', 'F', 'R' or 'B'"
729 // //message "Mailing control should be ' ' ,'N', 'I', 'J', 'S' ,'D' ,'L', '1' to '9', 'C', 'F', 'R' or 'B'"
730 // set lr-ok = false
731 // //exit(1)
732 // endif
733 // //z-dr-industry-sub-group
734 // if dr-industry-sub-group != SPACES
735 // get system-table
736 // key is 'CG' dr-industry-sub-group
737 // on error
738 // set ws-error-code = "0031"
739 // set ws-error-message = 'Invalid industry sub group '
740 // //message 'Invalid industry sub group '
741 // set lr-ok = false
742 // //exit(1)
743 // endon
744 // endif
745endprocedure