[Home] [Help]
28: process_mode IN VARCHAR2 );
29:
30: /* ------------------- End Local Procedures ------------------------------------ */
31:
32: /* This procedure handles the merge process for the CS_ACCESS_CONTROL_TEMPLATES.
33: It calls 5 seperate procedures to accomplish the task. The tasts are listed
34: below:
35: 1) Update the system_ship_to_site_use_id
36: 2) Update the system_install_site_use_id
59: arp_message.set_line('CP_CMERGE_BB1.MERGE()+');
60:
61: IF ( process_mode = 'LOCK' ) Then
62: arp_message.set_name('AR', 'AR_LOCKING_TABLE');
63: arp_message.set_token('TABLE_NAME', 'CS_ACCESS_CONTROL_TEMPLATES',FALSE );
64: message_text := 'The locking is done in block CSP_CMERGE_BB1';
65: arp_message.set_line(message_text);
66: ELSE
67: arp_message.set_name('AR', 'AR_UPDATING_TABLE');
64: message_text := 'The locking is done in block CSP_CMERGE_BB1';
65: arp_message.set_line(message_text);
66: ELSE
67: arp_message.set_name('AR', 'AR_UPDATING_TABLE');
68: arp_message.set_token('TABLE_NAME', 'CS_ACCESS_CONTROL_TEMPLATES',FALSE );
69: message_text := 'The merge is done in block CSP_CMERGE_BB1';
70: arp_message.set_line(message_text);
71: END IF;
72:
69: message_text := 'The merge is done in block CSP_CMERGE_BB1';
70: arp_message.set_line(message_text);
71: END IF;
72:
73: /* merge the CS_ACCESS_CONTROL_TEMPLATES table update system ship to site use id */
74:
75: message_text := '***-- Procedure CS_MERGE_SYS_SHIP_SITE_ID --**';
76: arp_message.set_line(message_text);
77:
79:
80: message_text := '***-- End CS_MERGE_SYS_SHIP_SITE_ID --**';
81: arp_message.set_line(message_text);
82:
83: /* merge the CS_ACCESS_CONTROL_TEMPLATES table update system install site use id */
84:
85: message_text := '***-- Procedure CS_MERGE_SYS_INSTALL_SITE_ID --**';
86: arp_message.set_line(message_text);
87:
89:
90: message_text := '***-- End CS_MERGE_SYS_INSTALL_SITE_ID --**';
91: arp_message.set_line(message_text);
92:
93: /* merge the CS_ACCESS_CONTROL_TEMPLATES table update cp ship to site use id */
94:
95: message_text := '***-- Procedure CS_MERGE_CP_SHIP_SITE_ID --**';
96: arp_message.set_line(message_text);
97:
99:
100: message_text := '***-- End CS_MERGE_CP_SHIP_SITE_ID --**';
101: arp_message.set_line(message_text);
102:
103: /* merge the CS_ACCESS_CONTROL_TEMPLATES table update cp install site use id */
104:
105: message_text := '***-- Procedure CS_MERGE_CP_INSTALL_SITE_ID --**';
106: arp_message.set_line(message_text);
107:
109:
110: message_text := '***-- End CS_MERGE_CP_INSTALL_SITE_ID --**';
111: arp_message.set_line(message_text);
112:
113: /* merge the CS_ACCESS_CONTROL_TEMPLATES table update the customer_id */
114:
115: message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
116: arp_message.set_line(message_text);
117:
119:
120: message_text := '***-- End CS_MERGE_CUSTOMER_ID --**';
121: arp_message.set_line(message_text);
122:
123: /* That the merge of CS_ACCESS_CONTROL_TEMPLATES is complete, use a cursor to
124: check to make sure all data has been updated. If not report it to the
125: log file. */
126:
127: CS_CHECK_MERGE_DATA ( req_id, set_number, process_mode );
125: log file. */
126:
127: CS_CHECK_MERGE_DATA ( req_id, set_number, process_mode );
128:
129: /* Report that the process for CS_ACCESS_CONTROL_TEMPLATES is complete */
130:
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_ACCESS_CONTROL_TEMPLATES **';
133: arp_message.set_line(message_text);
128:
129: /* Report that the process for CS_ACCESS_CONTROL_TEMPLATES is complete */
130:
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_ACCESS_CONTROL_TEMPLATES **';
133: arp_message.set_line(message_text);
134: ELSE
135: message_text := '** MERGE completed for table CS_ACCESS_CONTROL_TEMPLATES **';
136: arp_message.set_line(message_text);
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_ACCESS_CONTROL_TEMPLATES **';
133: arp_message.set_line(message_text);
134: ELSE
135: message_text := '** MERGE completed for table CS_ACCESS_CONTROL_TEMPLATES **';
136: arp_message.set_line(message_text);
137: END IF;
138:
139: arp_message.set_line('CP_CMERGE_BB1.MERGE()-');
141: END MERGE;
142:
143: /* -----------------------------------------------------------------------------*/
144:
145: /* Update the ship use site id of cs_access_control_templates */
146:
147: PROCEDURE CS_MERGE_SYS_SHIP_SITE_ID( req_id IN NUMBER,
148: set_number IN NUMBER,
149: process_mode IN VARCHAR2 ) IS
157: number_of_rows NUMBER;
158:
159: Cursor LOCK_SYS_SHIP_USE_ID ( req_id NUMBER, set_number NUMBER ) IS
160: SELECT system_ship_to_site_use_id
161: FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
162: WHERE
163: yt.system_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
164: FROM RA_CUSTOMER_MERGES RACM
165: WHERE RACM.PROCESS_FLAG = 'N'
186:
187: message_text := 'Updating the system_ship_to_site_use_id ( 1/5 )';
188: arp_message.set_line(message_text);
189:
190: UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
191: SET
192: yt.system_ship_to_site_use_id =
193: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
194: FROM RA_CUSTOMER_MERGES RACM
236: raise;
237:
238: END CS_MERGE_SYS_SHIP_SITE_ID;
239:
240: /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
241:
242: PROCEDURE CS_MERGE_SYS_INSTALL_SITE_ID ( req_id IN NUMBER,
243: set_number IN NUMBER,
244: process_mode IN VARCHAR2 ) IS
252: number_of_rows NUMBER;
253:
254: Cursor LOCK_SYS_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
255: SELECT system_install_site_use_id
256: FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
257: WHERE
258: yt.system_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
259: FROM RA_CUSTOMER_MERGES RACM
260: WHERE RACM.PROCESS_FLAG = 'N'
280: message_text :=
281: 'Starting to update the system_install_site_use_id ( 2/5 )';
282: arp_message.set_line(message_text);
283:
284: UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
285: SET
286: yt.system_install_site_use_id =
287: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
288: FROM RA_CUSTOMER_MERGES RACM
330: raise;
331:
332: END CS_MERGE_SYS_INSTALL_SITE_ID;
333:
334: /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
335:
336: PROCEDURE CS_MERGE_CP_SHIP_SITE_ID( req_id IN NUMBER,
337: set_number IN NUMBER,
338: process_mode IN VARCHAR2 ) IS
346: number_of_rows NUMBER;
347:
348: Cursor LOCK_CP_SHIP_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
349: SELECT cp_ship_to_site_use_id
350: FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
351: WHERE
352: yt.cp_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
353: FROM RA_CUSTOMER_MERGES RACM
354: WHERE RACM.PROCESS_FLAG = 'N'
373:
374: message_text := 'Starting to update the cp_ship_to_site_use_id ( 3/5 )';
375: arp_message.set_line(message_text);
376:
377: UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
378: SET
379: yt.cp_ship_to_site_use_id =
380: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
381: FROM RA_CUSTOMER_MERGES RACM
422: raise;
423:
424: END CS_MERGE_CP_SHIP_SITE_ID;
425:
426: /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
427:
428: PROCEDURE CS_MERGE_CP_INSTALL_SITE_ID ( req_id IN NUMBER,
429: set_number IN NUMBER,
430: process_mode IN VARCHAR2 ) IS
438: number_of_rows NUMBER;
439:
440: Cursor LOCK_CP_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
441: SELECT cp_install_site_use_id
442: FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
443: WHERE
444: yt.cp_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
445: FROM RA_CUSTOMER_MERGES RACM
446: WHERE RACM.PROCESS_FLAG = 'N'
466: message_text :=
467: 'Starting to update the cp_install_site_use_id ( 4/5 )';
468: arp_message.set_line(message_text);
469:
470: UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
471: SET
472: yt.cp_install_site_use_id =
473: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
474: FROM RA_CUSTOMER_MERGES RACM
515: raise;
516:
517: END CS_MERGE_CP_INSTALL_SITE_ID;
518:
519: /* This process updates the customer_id of the CS_ACCESS_CONTROL_TEMPLATES table */
520:
521: PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
522: set_number IN NUMBER,
523: process_mode IN VARCHAR2 ) IS
531: number_of_rows NUMBER;
532:
533: Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
534: SELECT yt.customer_id
535: FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
536: WHERE
537: yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
538: FROM RA_CUSTOMER_MERGES RACM
539: WHERE RACM.PROCESS_FLAG = 'N'
557:
558: message_text := 'Starting to update the customer_id ( 5/5 )';
559: arp_message.set_line(message_text);
560:
561: UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
562: SET
563: yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
564: FROM RA_CUSTOMER_MERGES RACM
565: WHERE yt.customer_id = DUPLICATE_ID
629: CURSOR CS_CHECK IS
630: SELECT
631: DISTINCT
632: cs.access_control_template_id
633: FROM CS_ACCESS_CONTROL_TEMPLATES CS,
634: RA_CUSTOMER_MERGES RACM
635: WHERE
636: RACM.PROCESS_FLAG = 'N' AND
637: RACM.REQUEST_ID = req_id AND
639: ( (( cs.customer_id = RACM.CUSTOMER_ID AND
640: cs.system_ship_to_site_use_id <> racm.customer_site_id AND
641: cs.system_ship_to_site_use_id IS NOT NULL ) AND
642: ( cs.customer_id NOT IN ( select racm.customer_id
643: from CS_ACCESS_CONTROL_TEMPLATES CS,
644: RA_CUSTOMER_MERGES RACM
645: where cs.customer_id = RACM.CUSTOMER_ID AND
646: cs.system_ship_to_site_use_id = racm.customer_site_id or
647: cs.system_ship_to_site_use_id IS NULL ))) AND
648: (( cs.customer_id = RACM.CUSTOMER_ID AND
649: cs.system_install_site_use_id <> racm.customer_site_id AND
650: cs.system_install_site_use_id IS NOT NULL ) AND
651: ( cs.customer_id NOT IN ( select racm.customer_id
652: from CS_ACCESS_CONTROL_TEMPLATES CS,
653: RA_CUSTOMER_MERGES RACM
654: where cs.customer_id = RACM.CUSTOMER_ID AND
655: cs.system_install_site_use_id = racm.customer_site_id or
656: cs.system_install_site_use_id IS NULL ))) AND
657: (( cs.customer_id = RACM.CUSTOMER_ID AND
658: cs.cp_install_site_use_id <> racm.customer_site_id AND
659: cs.cp_install_site_use_id IS NOT NULL ) AND
660: ( cs.customer_id NOT IN ( select racm.customer_id
661: from CS_ACCESS_CONTROL_TEMPLATES CS,
662: RA_CUSTOMER_MERGES RACM
663: where cs.customer_id = RACM.CUSTOMER_ID AND
664: cs.cp_install_site_use_id = racm.customer_site_id or
665: cs.cp_install_site_use_id IS NULL ))) AND
666: (( cs.customer_id = RACM.CUSTOMER_ID AND
667: cs.cp_ship_to_site_use_id <> racm.customer_site_id AND
668: cs.cp_ship_to_site_use_id IS NOT NULL ) AND
669: ( cs.customer_id NOT IN ( select racm.customer_id
670: from CS_ACCESS_CONTROL_TEMPLATES CS,
671: RA_CUSTOMER_MERGES RACM
672: where cs.customer_id = RACM.CUSTOMER_ID AND
673: cs.cp_ship_to_site_use_id = racm.customer_site_id or
674: cs.cp_ship_to_site_use_id IS NULL )) ));