[Home] [Help]
28: process_mode IN VARCHAR2 );
29:
30: /* ------------------- End Local Procedures ------------------------------------ */
31:
32: /* This procedure handles the merge process for the CS_TEMPLATES_INTERFACE.
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_BB4.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_TEMPLATES_INTERFACE',FALSE );
64: message_text := 'The locking is done in block CSP_CMERGE_BB4';
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_BB4';
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_TEMPLATES_INTERFACE',FALSE );
69: message_text := 'The merge is done in block CSP_CMERGE_BB4';
70: arp_message.set_line(message_text);
71: END IF;
72:
69: message_text := 'The merge is done in block CSP_CMERGE_BB4';
70: arp_message.set_line(message_text);
71: END IF;
72:
73: /* merge the CS_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE is complete */
130:
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_TEMPLATES_INTERFACE **';
133: arp_message.set_line(message_text);
128:
129: /* Report that the process for CS_TEMPLATES_INTERFACE is complete */
130:
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_TEMPLATES_INTERFACE **';
133: arp_message.set_line(message_text);
134: ELSE
135: message_text := '** MERGE completed for table CS_TEMPLATES_INTERFACE **';
136: arp_message.set_line(message_text);
131: IF ( process_mode = 'LOCK' ) Then
132: message_text := '** LOCKING completed for table CS_TEMPLATES_INTERFACE **';
133: arp_message.set_line(message_text);
134: ELSE
135: message_text := '** MERGE completed for table CS_TEMPLATES_INTERFACE **';
136: arp_message.set_line(message_text);
137: END IF;
138:
139: arp_message.set_line('CP_CMERGE_BB4.MERGE()-');
141: END MERGE;
142:
143: /* -----------------------------------------------------------------------------*/
144:
145: /* Update the ship use site id of CS_TEMPLATES_INTERFACE */
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_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE yt
191: SET
192: yt.system_ship_to_site_use_id =
193: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
194: FROM RA_CUSTOMER_MERGES RACM
232: raise;
233:
234: END CS_MERGE_SYS_SHIP_SITE_ID;
235:
236: /* Update the ship use site id of CS_TEMPLATES_INTERFACE */
237:
238: PROCEDURE CS_MERGE_SYS_INSTALL_SITE_ID ( req_id IN NUMBER,
239: set_number IN NUMBER,
240: process_mode IN VARCHAR2 ) IS
248: number_of_rows NUMBER;
249:
250: Cursor LOCK_SYS_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
251: SELECT system_install_site_use_id
252: FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
253: WHERE
254: yt.system_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
255: FROM RA_CUSTOMER_MERGES RACM
256: WHERE RACM.PROCESS_FLAG = 'N'
276: message_text :=
277: 'Starting to update the system_install_site_use_id ( 2/5 )';
278: arp_message.set_line(message_text);
279:
280: UPDATE CS_TEMPLATES_INTERFACE yt
281: SET
282: yt.system_install_site_use_id =
283: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
284: FROM RA_CUSTOMER_MERGES RACM
322: raise;
323:
324: END CS_MERGE_SYS_INSTALL_SITE_ID;
325:
326: /* Update the ship use site id of CS_TEMPLATES_INTERFACE */
327:
328: PROCEDURE CS_MERGE_CP_SHIP_SITE_ID( req_id IN NUMBER,
329: set_number IN NUMBER,
330: process_mode IN VARCHAR2 ) IS
338: number_of_rows NUMBER;
339:
340: Cursor LOCK_CP_SHIP_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
341: SELECT cp_ship_to_site_use_id
342: FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
343: WHERE
344: yt.cp_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
345: FROM RA_CUSTOMER_MERGES RACM
346: WHERE RACM.PROCESS_FLAG = 'N'
365:
366: message_text := 'Starting to update the cp_ship_to_site_use_id ( 3/5 )';
367: arp_message.set_line(message_text);
368:
369: UPDATE CS_TEMPLATES_INTERFACE yt
370: SET
371: yt.cp_ship_to_site_use_id =
372: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
373: FROM RA_CUSTOMER_MERGES RACM
410: raise;
411:
412: END CS_MERGE_CP_SHIP_SITE_ID;
413:
414: /* Update the ship use site id of CS_TEMPLATES_INTERFACE */
415:
416: PROCEDURE CS_MERGE_CP_INSTALL_SITE_ID ( req_id IN NUMBER,
417: set_number IN NUMBER,
418: process_mode IN VARCHAR2 ) IS
426: number_of_rows NUMBER;
427:
428: Cursor LOCK_CP_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
429: SELECT cp_install_site_use_id
430: FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
431: WHERE
432: yt.cp_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
433: FROM RA_CUSTOMER_MERGES RACM
434: WHERE RACM.PROCESS_FLAG = 'N'
454: message_text :=
455: 'Starting to update the cp_install_site_use_id ( 4/5 )';
456: arp_message.set_line(message_text);
457:
458: UPDATE CS_TEMPLATES_INTERFACE yt
459: SET
460: yt.cp_install_site_use_id =
461: ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
462: FROM RA_CUSTOMER_MERGES RACM
499: raise;
500:
501: END CS_MERGE_CP_INSTALL_SITE_ID;
502:
503: /* This process updates the customer_id of the CS_TEMPLATES_INTERFACE table */
504:
505: PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
506: set_number IN NUMBER,
507: process_mode IN VARCHAR2 ) IS
515: number_of_rows NUMBER;
516:
517: Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
518: SELECT yt.customer_id
519: FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
520: WHERE
521: yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
522: FROM RA_CUSTOMER_MERGES RACM
523: WHERE RACM.PROCESS_FLAG = 'N'
541:
542: message_text := 'Starting to update the customer_id ( 5/5 )';
543: arp_message.set_line(message_text);
544:
545: UPDATE CS_TEMPLATES_INTERFACE yt
546: SET
547: yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
548: FROM RA_CUSTOMER_MERGES RACM
549: WHERE yt.customer_id = DUPLICATE_ID
608:
609: CURSOR CS_CHECK IS
610: SELECT
611: DISTINCT
612: cs.templates_interface_id
613: FROM CS_TEMPLATES_INTERFACE CS,
614: RA_CUSTOMER_MERGES RACM
615: WHERE
616: RACM.PROCESS_FLAG = 'N' AND
609: CURSOR CS_CHECK IS
610: SELECT
611: DISTINCT
612: cs.templates_interface_id
613: FROM CS_TEMPLATES_INTERFACE CS,
614: RA_CUSTOMER_MERGES RACM
615: WHERE
616: RACM.PROCESS_FLAG = 'N' AND
617: RACM.REQUEST_ID = req_id AND
619: ((( cs.customer_id = RACM.CUSTOMER_ID AND
620: cs.system_ship_to_site_use_id <> racm.customer_site_id AND
621: cs.system_ship_to_site_use_id IS NOT NULL ) AND
622: ( cs.customer_id NOT IN ( select racm.customer_id
623: from CS_TEMPLATES_INTERFACE CS,
624: RA_CUSTOMER_MERGES RACM
625: where cs.customer_id = RACM.CUSTOMER_ID AND
626: cs.system_ship_to_site_use_id = racm.customer_site_id or
627: cs.system_ship_to_site_use_id IS NULL ))) AND
628: (( cs.customer_id = RACM.CUSTOMER_ID AND
629: cs.system_install_site_use_id <> racm.customer_site_id AND
630: cs.system_install_site_use_id IS NOT NULL ) AND
631: ( cs.customer_id NOT IN ( select racm.customer_id
632: from CS_TEMPLATES_INTERFACE CS,
633: RA_CUSTOMER_MERGES RACM
634: where cs.customer_id = RACM.CUSTOMER_ID AND
635: cs.system_install_site_use_id = racm.customer_site_id or
636: cs.system_install_site_use_id IS NULL ))) AND
637: (( cs.customer_id = RACM.CUSTOMER_ID AND
638: cs.cp_install_site_use_id <> racm.customer_site_id AND
639: cs.cp_install_site_use_id IS NOT NULL ) AND
640: ( cs.customer_id NOT IN ( select racm.customer_id
641: from CS_TEMPLATES_INTERFACE CS,
642: RA_CUSTOMER_MERGES RACM
643: where cs.customer_id = RACM.CUSTOMER_ID AND
644: cs.cp_install_site_use_id = racm.customer_site_id or
645: cs.cp_install_site_use_id IS NULL ))) AND
646: (( cs.customer_id = RACM.CUSTOMER_ID AND
647: cs.cp_ship_to_site_use_id <> racm.customer_site_id AND
648: cs.cp_ship_to_site_use_id IS NOT NULL ) AND
649: ( cs.customer_id NOT IN ( select racm.customer_id
650: from CS_TEMPLATES_INTERFACE CS,
651: RA_CUSTOMER_MERGES RACM
652: where cs.customer_id = RACM.CUSTOMER_ID AND
653: cs.cp_ship_to_site_use_id = racm.customer_site_id or
654: cs.cp_ship_to_site_use_id IS NULL ))) );