[Home] [Help]
139: SELECT distinct CUSTOMER_MERGE_HEADER_ID
140: ,yt.ord_oneclick_id
141: ,yt.CUST_ACCOUNT_ID
142: ,yt.party_id
143: FROM IBE_ORD_ONECLICK_ALL yt, ra_customer_merges m
144: WHERE
145: yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
146: AND m.process_flag = 'N'
147: AND m.request_id = req_id
259: SELECT distinct CUSTOMER_MERGE_HEADER_ID
260: ,yt.SHP_LIST_ID
261: ,yt.CUST_ACCOUNT_ID
262: ,yt.party_id
263: FROM IBE_SH_SHP_LISTS_ALL yt, ra_customer_merges m
264: WHERE
265: yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
266: AND m.process_flag = 'N'
267: AND m.request_id = req_id
416:
417: --cursor to get
418: Cursor C_ACTIVE_QUOTE_FROM is
419: Select a.quote_header_id, a.cust_account_id, a.party_id, b.quote_name, racm.customer_merge_id
420: from IBE_ACTIVE_QUOTES_ALL a, ASO_QUOTE_HEADERS_ALL b, RA_CUSTOMER_MERGES RACM
421: Where a.quote_header_id = b.quote_header_id (+)
422: and a.party_id = b.party_id (+)
423: and a.cust_account_id = b.cust_account_id (+)
424: and a.cust_account_id = racm.duplicate_id
569: /*retrive
570: Cursor C_SHARED_QUOTE_FROM is
571: Select distinct customer_merge_header_id,quote_header_id,
572: i.party_id, RACM.DUPLICATE_ID, RACM.CUSTOMER_ID,quote_sharee_id
573: from IBE_SH_QUOTE_ACCESS i, RA_CUSTOMER_MERGES RACM
574: Where i.cust_account_id = RACM.DUPLICATE_ID
575: AND RACM.PROCESS_FLAG='N'
576: AND RACM.REQUEST_ID = req_id
577: AND RACM.SET_NUMBER = set_num;
727: IS
728:
729: g_count NUMBER;
730: p_request_id NUMBER;
731: p_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
732: p_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
733: p_allow_merge VARCHAR2(1) := 'Y';
734: p_customer_type RA_CUSTOMER_MERGES.CUSTOMER_TYPE%TYPE;
735: l_from_quote_id NUMBER;
728:
729: g_count NUMBER;
730: p_request_id NUMBER;
731: p_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
732: p_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
733: p_allow_merge VARCHAR2(1) := 'Y';
734: p_customer_type RA_CUSTOMER_MERGES.CUSTOMER_TYPE%TYPE;
735: l_from_quote_id NUMBER;
736: l_from_party_id NUMBER;
730: p_request_id NUMBER;
731: p_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
732: p_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
733: p_allow_merge VARCHAR2(1) := 'Y';
734: p_customer_type RA_CUSTOMER_MERGES.CUSTOMER_TYPE%TYPE;
735: l_from_quote_id NUMBER;
736: l_from_party_id NUMBER;
737: l_from_acct_id NUMBER;
738: l_to_acct_id NUMBER;
744:
745: CURSOR C is
746: SELECT 'X' from IBE_SH_SHP_LISTS_ALL ISA
747: WHERE ISA.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
748: FROM RA_CUSTOMER_MERGES RACM
749: WHERE RACM.PROCESS_FLAG='N'
750: AND RACM.REQUEST_ID = request_id
751: AND RACM.SET_NUMBER = set_number)
752: FOR UPDATE NOWAIT;
753:
754: CURSOR C1 is
755: SELECT 'X' from IBE_ORD_ONECLICK_ALL IOO
756: WHERE IOO.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
757: FROM RA_CUSTOMER_MERGES RACM
758: WHERE RACM.PROCESS_FLAG='N'
759: AND RACM.REQUEST_ID = request_id
760: AND RACM.SET_NUMBER = set_number)
761: FOR UPDATE NOWAIT;
762:
763: CURSOR C2 is
764: SELECT 'X' from IBE_SH_QUOTE_ACCESS ISQ
765: WHERE ISQ.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
766: FROM RA_CUSTOMER_MERGES RACM
767: WHERE RACM.PROCESS_FLAG='N'
768: AND RACM.REQUEST_ID = request_id
769: AND RACM.SET_NUMBER = set_number)
770: FOR UPDATE NOWAIT;
771:
772: CURSOR C3 is
773: SELECT 'X' from IBE_ACTIVE_QUOTES_ALL IAQ
774: WHERE IAQ.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
775: FROM RA_CUSTOMER_MERGES RACM
776: WHERE RACM.PROCESS_FLAG='N'
777: AND RACM.REQUEST_ID = Request_id
778: AND RACM.SET_NUMBER = Set_Number)
779: FOR UPDATE NOWAIT;
780:
781: --2940366 add customer type, change requestID
782: CURSOR C_CUST (req_id NUMBER) is
783: SELECT RACM.CUSTOMER_ID,RACM.DUPLICATE_ID,RACM.CUSTOMER_TYPE
784: FROM RA_CUSTOMER_MERGES RACM
785: WHERE RACM.PROCESS_FLAG='N'
786: AND RACM.REQUEST_ID = req_id
787: AND RACM.SET_NUMBER = set_number;
788: