undef dblink

-- ========== PAYROLL ==========

delete from QLK2.PAYROLL&&dblink where PAYSYS_ID = 15123;

insert into QLK2.PAYROLL&&dblink (PAYSYS_ID, CONTACT_PERSON, DESCRIPTION, SHORT_NAME, EMPL_FLAG, ACTIVE, COMMS_METHOD, NO_EMPL_FILES, CUR_PERIOD, SECTOR, FORMAT_ID, BANK_DETAILS_REQUIRED)
values (15123, 'NONE', substr( 'Brits rusoord', 1, 50 ), substr( replace( upper( 'BR' ), ' ', '' ), 1, 50 ), 'N', 'Y', 'Q', NULL, '201605', '02', 5016, 'N' );

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAYROLL', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');


-- ========== PAYROLL_UNION_INSTITUTION ==========

delete from QLK2.PAYROLL_UNION_INSTITUTION&&dblink where INSTIT_ID = '363' and PAYSYS_ID = 15123;

insert into QLK2.PAYROLL_UNION_INSTITUTION&&dblink (PAYSYS_ID, UNION_ID, INSTIT_ID, ERROR_SEQ_NO, TRANSACTION_SEQ_NO, OUTPUT_SEQ_NO)
values (15123, 15, 363, 0, 0, 0);

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAYROLL_UNION_INSTITUTION', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');


-- ========== PAYROLL_UNION ==========

delete from PAYROLL_UNION&&dblink where UNION_ID = 15 and PAYSYS_ID = 15123; 

insert into QLK2.PAYROLL_UNION&&dblink (PAYSYS_ID, UNION_ID, REGISTERED, REGISTRATION_FEE, ERROR_SEQ_NO, TRANSACTION_SEQ_NO, SUBMISSION_FREQ, FTP_IP, FTP_USERNAME, FTP_PASSWORD, OUTPUT_SEQ_NO, REF_FLAG, PAYROLL_INTERFACE, PAYMENT_METHOD)
values (15123, 15, 'R', 0, 0, 0, 1, NULL, NULL, NULL, 0, 0, 2, 1);

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAYROLL_UNION', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== PAYROLL_UNION_SUBS ==========

delete from QLK2.PAYROLL_UNION_SUBS&&dblink where UNION_ID = 15 and PAYSYS_ID =15123; 

insert into QLK2.PAYROLL_UNION_SUBS&&dblink (PAYROLL_GROUP_ID, UNION_ID, SUBSCRIPTION_ID, AMOUNT, PERCENTAGE, PAYSYS_ID, DEDUCT_TYPE, INSTIT_ID)
values (11, 15, 10, 77, 0, 15123, '0028', 363);

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAYROLL_UNION_SUBS', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== PAYROLL_SUBS_GROUP ==========

delete from PAYROLL_SUBS_GROUP&&dblink where PAYSYS_ID = 15123 and DEFAULT_SUBSCRIPTION_ID = 10;

insert into PAYROLL_SUBS_GROUP&&dblink (PAYROLL_GROUP_ID, DESCRIPTION, DEDUCT_TYPE, INSTIT_ID, DEFAULT_SUBSCRIPTION_ID)
values (15123, 'MEMBERSHIP FEES', '0028', 0, 10);

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAYROLL_SUBS_GROUP', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== DEPARTMENT ==========

delete from DEPARTMENT&&dblink where PAYSYS_ID = 15123 and DEPARTMENT_NO = 'BR';

insert into QLK2.DEPARTMENT&&dblink ( PAYSYS_ID, DEPARTMENT_NO, DEPARTMENT_NAME )
values ( 15123, 'BR', 'Brits rusoord' );

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'DEPARTMENT', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== PAY_POINT ==========

delete from QLK2.PAY_POINT&&dblink where PAYSYS_ID = 15123 and PAY_POINT = '15123BR';

insert into QLK2.PAY_POINT&&dblink ( PAY_POINT, PAYSYS_ID, DEPARTMENT_NO, PAY_POINT_DESC )
values ( '15123BR', 15123, 'BR', 'Brits rusoord' );

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'PAY_POINT', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== TEL_NUMBERS ==========

delete from QLK2.TEL_NUMBERS&&dblink where PAYSYS_ID = 15123 and MEMBER_NO is null;

insert into QLK2.TEL_NUMBERS&&dblink  ( TEL_ID, TEL_NO, TEL_TYPE, UNION_ID, PAY_POINT, PAYSYS_ID, DEPARTMENT_NO, MEMBER_NO, PROVIDER_ID, BARGAIN_COUNCIL_ID, 
ACTIVITY_LOG_ID, GROUPING1, GROUPING2, GROUPING3, GROUPING4, MEMBER_RELATIONS_ID, STATUS, STATUS_DATE)
values ( SEQ_TEL_NUMBERS.NEXTVAL, '(012) 252 7043 / 5435', '2', 15, '15123BR', 15123, 'BR', null, 0, null, -1925519, 13, null, null, null, null, null, null );

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'TEL_NUMBERS', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');

-- ========== ADDRESS ==========

delete from QLK2.ADDRESS&&dblink where UNION_ID = 15 and PAYSYS_ID = 15123 and DEPARTMENT_NO = 'BR' and PAY_POINT = 'BR';

insert into QLK2.ADDRESS&&dblink ( ADDR_TYPE, ADDRESS1, ADDRESS2, ADDRESS3, CITY, ADDRESS_ID, PAY_POINT, UNION_ID, MEMBER_NO, DEPARTMENT_NO, POSTAL_CODE, 
PAYSYS_ID, PROVINCE_ID, PROVIDER_ID, ACTIVITY_LOG_ID, GROUPING1, GROUPING2, GROUPING3, GROUPING4, MEMBER_RELATIONS_ID, STATUS, STATUS_DATE)
values ( '10', 'Kerkstr 62 A', 'Posbus 2323', '', 'Brits', SEQ_ADDRESS.NEXTVAL, '15123BR', 15, null, 'BR', '0250', 15123, null, null, 1925519, 2, null, null, null, null, null, sysdate );

insert into DBA_HIST&&dblink( REQUEST_NO, TASK_NO, INITIATOR, OBJECT_TYPE, OBJECT_NAME, ACTION, SCRIPT_NAME, DATE_TIME_STAMP, COMMENTS ) 
values (19255, 19, 'mariskal', 'TBL', 'ADDRESS', 'DCL', '19255_Task19_NewUnionPayroll_15123.sql', sysdate, 'New payroll - 19255');


-- ========== ********** END OF SCRIPT ********** ==========

