CA Service Management

Factory cnt < ca_contact, usp_contact >

Attributes:

  id <contact_uuid>    UUID UNIQUE

  producer_id          LOCAL STRING(20)

  persistent_id        LOCAL STRING(60)

  supervisor_contact_uuid SREL -> cnt.id

  last_mod_dt last_update_date DATE

  creation_date        DATE

  last_name            STRING(100) REQUIRED SKEY

  first_name          STRING(100) SKEY

  middle_name          STRING(100) SKEY

  alias                STRING(30)

  delete_flag inactive SREL -> actbool.enum REQUIRED

  version_number      INTEGER

  last_mod_by last_update_user STRING(64)

  creation_user        STRING(64)

  exclude_registration INTEGER

  delete_time          DATE

  userid              STRING(100)

  phone_number pri_phone_number STRING(40)

  alt_phone alt_phone_number STRING(40)

  mobile_phone mobile_phone_number STRING(40)

  type contact_type    SREL -> ctp.id

  billing_code cost_center SREL -> cost_cntr.id

  location location_uuid SREL -> loc.id

  dept department      SREL -> dept.id

  organization organization_uuid SREL -> org.id

  admin_org admin_organization_uuid SREL -> org.id

  company company_uuid SREL -> ca_cmpny.id

  notes comment<comments> STRING(255)

  fax_phone fax_number STRING(40)

  beeper_phone pager_number STRING(40)

  email_address        STRING(120)

  pemail_address pager_email_address STRING(120)

  room_location        STRING(30)

  contact_num alternate_identifier STRING(30)

  position job_title  SREL -> position.id

  tenant_group        SREL -> tenant_group.id

  cenv                BREL <- lrel_cenv_cntref.cnt (LREL nr) {cnt = ?}

  cntntf              BREL <- lrel_notify_list_cntntf.cnt (LREL cr) {cnt = ?}

  service_locs        BREL <- lrel_svc_locs_svc_groups.cnt (LREL loc) {cnt = ?}

  service_pcat        BREL <- lrel_svc_grps_svc_pcat.cnt (LREL pcat) {cnt = ?}

  service_chgcat      BREL <- lrel_svc_grps_svc_chgcat.cnt (LREL chgcat) {cnt = ?}

  service_isscat      BREL <- lrel_svc_grps_svc_isscat.cnt (LREL isscat) {cnt = ?}

  service_wftpl        BREL <- lrel_svc_grps_svc_wftpl.cnt (LREL wftpl) {cnt = ?}

  att_ntfrlist        BREL <- lrel_ntfr_cntlist_att_ntfrlist.cnt (LREL ntfr) {cnt = ?}

  cntissntf            BREL <- lrel_notify_list_cntissntf.cnt (LREL iss) {cnt = ?}

  cntchgntf            BREL <- lrel_notify_list_cntchgntf.cnt (LREL chg) {cnt = ?}

  macro_ntf            BREL <- lrel_att_cntlist_macro_ntf.cnt (LREL macro) {cnt = ?}

  mgs_ntf              BREL <- lrel_dist_cntlist_mgs_ntf.cnt (LREL mgs) {cnt = ?}

  member_list          BREL <- grpmem.group (LREL member) {group = ?}

  group_list          BREL <- grpmem.member (LREL group) {member = ?}

  cnthandling_list    BREL <- contact_handling.contact (LREL special_handling) {contact = ?}

  web_url              LOCAL STRING(0) DERIVED('http://<servername>:8080/CAisd/pdmweb.exe?OP=SEARCH+FACTORY=cnt+SKIPLIST=1+QBE.EQ.id=' || CAST(id AS NVARCHAR))

  all_creq            BREL <- cr.customer {customer = ?}

  all_chg              BREL <- chg.requestor {requestor = ?}

  all_iss              BREL <- iss.requestor {requestor = ?}

  all_open_creq        QREL <- cr {customer = ? and active = 1}

  combo_name          LOCAL STRING(0) DERIVED(last_name || isnull(', ' || first_name || ' ' || middle_name))

  combo_name2          LOCAL STRING(0) DERIVED(last_name || ',' || isnull(first_name) || ',' || isnull(middle_name))

  log_reader          BREL <- lr.contact {contact = ?}

  roles                BREL <- cnt_role.contact {contact = ?}

  active_special_handling QREL <- contact_handling {contact = ? and special_handling.delete_flag = 0}

  workload            QREL <- cr {assignee = ? and active = 1}

  workload_chg        QREL <- chg {assignee = ? and active = 1}

  workload_iss        QREL <- iss {assignee = ? and active = 1}

  workload_wf          QREL <- wf {assignee = ? and status = 'PEND'}

  workload_isswf      QREL <- iss_wf {assignee = ? and status = 'PEND'}

  cr_nf_list          BREL <- qp_cr_cnt.cnt_srel {cnt_srel = ?}

  chg_nf_list          BREL <- qp_chg_cnt.cnt_srel {cnt_srel = ?}

  iss_nf_list          BREL <- qp_iss_cnt.cnt_srel {cnt_srel = ?}

  change_tasks        QREL <- wf {assignee = ? AND status.allow_task_update = 1}

  issue_tasks          QREL <- iss_wf {assignee = ? AND status.allow_task_update = 1}

  tenant              SREL -> tenant.id REQUIRED

  access_type c_acctyp_id SREL -> acctyp.id

  notify_method1 c_cm_id1 SREL -> cmth.id

  notify_method2 c_cm_id2 SREL -> cmth.id

  notify_method3 c_cm_id3 SREL -> cmth.id

  notify_method4 c_cm_id4 SREL -> cmth.id

  notify_ws1 c_ws_id1  SREL -> wrkshft.persistent_id

  notify_ws2 c_ws_id2  SREL -> wrkshft.persistent_id

  notify_ws3 c_ws_id3  SREL -> wrkshft.persistent_id

  notify_ws4 c_ws_id4  SREL -> wrkshft.persistent_id

  domain c_domain      SREL -> dmn.id

  vendor c_vendor      SREL -> ca_cmpny.id

  service_type c_service_type SREL -> no_contract_sdsc.code

  timezone c_timezone  SREL -> tz.code

  integration_cnt      INTEGER

  confirm_save        INTEGER

  notify_urgency1      LOCAL SREL -> noturg.enum

  notify_urgency2      LOCAL SREL -> noturg.enum

  notify_urgency3      LOCAL SREL -> noturg.enum

  notify_urgency4      LOCAL SREL -> noturg.enum

  schedule c_schedule  SREL -> wrkshft.persistent_id

  available c_available INTEGER

  pgroups              LOCAL STRING(0)

ldap_dn              STRING(512)

  auto_bias_wf        LOCAL INTEGER

  auto_bias_isswf      LOCAL INTEGER

  linked_id_usp_contact id<contact_uuid> UUID UNIQUE

  cost                DOUBLE

  currency_type        SREL -> usp_currency.id

  KD_APPROVAL          BREL <- CI_ACTIONS_ALTERNATE.CONTACT_ID {CONTACT_ID = ?}

  audit_userid        LOCAL SREL -> cnt.id TENANCY_UNRESTRICTED

  sap_personID        STRING(32)

TABLE ca_contact

  admin_organization_uuid alias alt_phone_number alternate_identifier comment company_uuid contact_type cost_center creation_date creation_user delete_time department email_address exclude_registration fax_number first_name floor_location id inactive job_function job_title last_name last_update_date last_update_user location_uuid mail_stop middle_name mobile_phone_number organization_uuid pager_email_address pager_number pri_phone_number room_location supervisor_contact_uuid tenant tenant_group userid version_number

  { "" ,"" ,"" ,"" ,"Usuário para a integração do Asset Management" ,"", "2307" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", "16226C765005B94E957E0F477DEF1B1C" ,"0" ,"" ,"" ,"System_AM_User",

  "06/17/2015 15:52:24" ,"ServiceDesk" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", "C6F2D388395F734EBC4C6AEEBCBF68E4" ,"" ,"System_AM_User" ,"1" }

TABLE ca_contact

userid  last_name email_address alt_phone_number

{"login.name","user Last Name ","my@emailaddress.com",""}

command = C:\PROGRA~2/CA/SERVIC~1\bin\dbload -i -v  CARGA_~1.TXT

Processing CARGA_~1.TXT

LOADING DATA

        userid: [login.name]

        last_name: [user Last Name]

        email_address: [my@emailaddress.com]

        alt_phone_number: []

        alias: []

        id: [3988E217CE309B42B1250564894A02CD]

        inactive: [0]

        tenant: [00]

ca_contact :

Rows  :1

Inserts:1

Updates:0

Errors : 0

MaxKey : 0

Ending Totals:

Total tables :1

Total rows  :1

Total inserts:1

Total updates:0

Total errors: 0

Highest Table keys processed    :ca_contact(0)

Files processed: 1

TABLE usp_contact

userid access notify_method1 notify_method2 notify_method3 notify_method4

{"name.lastname", "Cliente" , "Normal","Normal","Normal","Normal"}

Deref

{

input = userid

output = id

rule = "select id FROM ca_contact WHERE userid=?"

}

pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)

pdm_deref -v -s cnt_spec.txt < load_uspcnt.txt > load_uspcnt_exit_A

.txt

TABLE usp_contact

      id access notify_method1 notify_method2 notify_method3 notify_method4

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "Cliente", "Normal", "Normal",

     "Normal", "Normal" }

Deref

{

input = access

output = c_acctyp_id

rule = "select id FROM Access_Type_v2 WHERE sym=?"

}

06. Run Dref Command use syntax

pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)

pdm_deref -v -s Access_Type_spec.txt < load_uspcnt_exit_A.txt > load_uspcnt_exit_B.txt

TABLE usp_contact

      id c_acctyp_id notify_method1

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "Normal", }

Deref

{

input = notify_method1

output = notify_urgency1

rule = "select id FROM Notification_Urgency WHERE sym=?"

}

09. Run Dref Command use syntax

pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)

pdm_deref -v -s notify_spec.txt < load_uspcnt_exit_B.txt > load_uspcnt_exit_C.txt

TABLE usp_contact

      id c_acctyp_id notify_urgency1

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "401" }

12. to load..

pdm_load -v -a -f load_uspcnt_exit_C.txt

TABLE usp_contact

id c_acctyp_id notify

{"4B066F502B009F48BC53A4CAC6DFA9AA", "10004" , "Email"}

Deref

{

input = notify

output = c_cm_id1

rule = "select id FROM Contact_Method WHERE sym=?"

}

03. Run command

pdm_deref -v -s cmth_spec.txt < load_uspcnt.txt > myexitfile.txt

TABLE usp_contact

      id c_acctyp_id c_cm_id1

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "1800" }