[Home] [Help]
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
89: OE_ORDER_HEADERS H
90: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
91: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
92: AND H.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
93: AND H.SOLD_TO_SITE_USE_ID = M4.DUPLICATE_SITE_ID(+)
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
146: OE_ORDER_HEADERS H
147: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
148: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
149: AND H.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
150: AND H.SOLD_TO_SITE_USE_ID = M4.DUPLICATE_SITE_ID(+)
187: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
188: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
189: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
190: FROM
191: OE_CUST_MERGES_GTT M1, OE_ORDER_HEADERS H
192: WHERE H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID
193: AND M1.PROCESS_FLAG='N'
194: AND M1.REQUEST_ID = req_id
195: AND M1.SET_NUMBER = set_num
199: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
200: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
201: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
202: FROM
203: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
204: WHERE H.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
205: AND M1.PROCESS_FLAG='N'
206: AND M1.REQUEST_ID = req_id
207: AND M1.SET_NUMBER = set_num
211: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
212: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
213: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
214: FROM
215: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
216: WHERE H.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
217: AND M1.PROCESS_FLAG='N'
218: AND M1.REQUEST_ID = req_id
219: AND M1.SET_NUMBER = set_num
223: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
224: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
225: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
226: FROM
227: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
228: WHERE H.SOLD_TO_SITE_USE_ID= M1.DUPLICATE_SITE_ID
229: AND M1.PROCESS_FLAG='N'
230: AND M1.REQUEST_ID = req_id
231: AND M1.SET_NUMBER = set_num
235: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
236: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
237: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
238: FROM
239: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
240: WHERE H.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
241: AND M1.PROCESS_FLAG='N'
242: AND M1.REQUEST_ID = req_id
243: AND M1.SET_NUMBER = set_num
247: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
248: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
249: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
250: FROM
251: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
252: WHERE H.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
253: AND M1.PROCESS_FLAG='N'
254: AND M1.REQUEST_ID = req_id
255: AND M1.SET_NUMBER = set_num
259: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
260: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
261: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
262: FROM
263: OE_CUST_MERGES_GTT M1,OE_ORDER_HEADERS H
264: WHERE H.END_CUSTOMER_ID= M1.DUPLICATE_ID
265: AND M1.PROCESS_FLAG='N'
266: AND M1.REQUEST_ID = req_id
267: AND M1.SET_NUMBER = set_num;
307: -- try to open table for update, if it fails the exception will
308: -- tell us that the merge was going to block
309:
310: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
311: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADERS_ALL', FALSE );
312:
313: open merge_headers;
314: close merge_headers;
315:
504: LAST_UPDATE_DATE,
505: LAST_UPDATED_BY
506: ) VALUES (
507: HZ_CUSTOMER_MERGE_LOG_s.nextval,
508: 'OE_ORDER_HEADERS_ALL',
509: MERGE_HEADER_ID_LIST(I),
510: hdr_header_id_tab(I),
511: old_hdr_sold_to_org_id_tab(I),
512: decode(hdr_sold_to_org_id_tab(I),NULL,old_hdr_sold_to_org_id_tab(I),hdr_sold_to_org_id_tab(i)),
535: end if;
536:
537:
538: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
539: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
540: IF hdr_header_id_tab.COUNT <> 0 THEN
541:
542: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
543: UPDATE OE_ORDER_HEADERS_ALL H
539: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
540: IF hdr_header_id_tab.COUNT <> 0 THEN
541:
542: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
543: UPDATE OE_ORDER_HEADERS_ALL H
544: SET sold_to_org_id = decode(hdr_sold_to_org_id_tab(i),null,sold_to_org_id,hdr_sold_to_org_id_tab(i)),
545: invoice_to_org_id = decode(hdr_invoice_to_org_id_tab(i),null,invoice_to_org_id,hdr_invoice_to_org_id_tab(i)),
546: ship_to_org_id = decode(hdr_ship_to_org_id_tab(i),null,ship_to_org_id,hdr_ship_to_org_id_tab(i)),
547: deliver_to_org_id = decode(hdr_deliver_to_org_id_tab(i),null,deliver_to_org_id,hdr_deliver_to_org_id_tab(i)),
665: -- Changed by Srini for MOAC
666: -- This MOAC change has been reverted.
667: -- For more information please
668: -- see the bug #5050382
669: OE_ORDER_HEADERS H
670: WHERE
671: ( hist.sold_to_org_id = m1.duplicate_id(+)
672: and hist.invoice_to_org_id = m2.duplicate_site_id(+)
673: and hist.ship_to_org_id = m3.duplicate_site_id(+)
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
736: OE_ORDER_HEADER_HISTORY HIST,
737: --Changed for MOAC
738: OE_ORDER_HEADERS_ALL H
739: WHERE
740: ( hist.sold_to_org_id = m1.duplicate_id(+)
741: and hist.invoice_to_org_id = m2.duplicate_site_id(+)
742: and hist.ship_to_org_id = m3.duplicate_site_id(+)
781: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
782: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
783: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
784: FROM
785: OE_CUST_MERGES_GTT M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
786: WHERE HIST.SOLD_TO_ORG_ID = M1.DUPLICATE_ID
787: AND M1.PROCESS_FLAG='N'
788: AND M1.REQUEST_ID = req_id
789: AND M1.SET_NUMBER = set_num
793: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
794: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
795: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
796: FROM
797: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
798: WHERE HIST.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
799: AND M1.PROCESS_FLAG='N'
800: AND M1.REQUEST_ID = req_id
801: AND M1.SET_NUMBER = set_num
805: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
806: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
807: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
808: FROM
809: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
810: WHERE HIST.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
811: AND M1.PROCESS_FLAG='N'
812: AND M1.REQUEST_ID = req_id
813: AND M1.SET_NUMBER = set_num
817: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
818: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
819: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
820: FROM
821: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
822: WHERE HIST.SOLD_TO_SITE_USE_ID= M1.DUPLICATE_SITE_ID
823: AND M1.PROCESS_FLAG='N'
824: AND M1.REQUEST_ID = req_id
825: AND M1.SET_NUMBER = set_num
829: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
830: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
831: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
832: FROM
833: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
834: WHERE HIST.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
835: AND M1.PROCESS_FLAG='N'
836: AND M1.REQUEST_ID = req_id
837: AND M1.SET_NUMBER = set_num
841: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
842: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
843: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
844: FROM
845: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
846: WHERE HIST.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
847: AND M1.PROCESS_FLAG='N'
848: AND M1.REQUEST_ID = req_id
849: AND M1.SET_NUMBER = set_num
853: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
854: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
855: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
856: FROM
857: OE_CUST_MERGES_GTT M1,OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
858: WHERE HIST.END_CUSTOMER_ID= M1.DUPLICATE_ID
859: AND M1.PROCESS_FLAG='N'
860: AND M1.REQUEST_ID = req_id
861: AND M1.SET_NUMBER = set_num
1628:
1629: CURSOR c4 is
1630: --changed for bug 3196900
1631: select /* MOAC_SQL_CHANGE */ a.HEADER_ID
1632: from OE_HEADER_ACKS a, OE_ORDER_HEADERS_all h
1633: where a.sold_to_org_id in
1634: (select m.duplicate_id
1635: from ra_customer_merges m
1636: where m.process_flag = 'N'
1667:
1668: CURSOR c7 is
1669: --changed for bug 3196900
1670: select a.HEADER_ID
1671: from OE_HEADER_ACKS a,OE_ORDER_HEADERS h
1672: where a.end_customer_id in
1673: (select m.duplicate_id
1674: from ra_customer_merges m
1675: where m.process_flag = 'N'
1984: and m.request_id = req_id
1985: and m.set_number = set_num)
1986: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1987: --changed for bug 3196900
1988: and a.header_id in (select header_id from oe_order_headers);
1989:
1990: g_count := sql%rowcount;
1991:
1992: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2205: and m.request_id = req_id
2206: and m.set_number = set_num)
2207: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
2208: --changed for bug 3196900
2209: and a.header_id in (select header_id from oe_order_headers);
2210:
2211: g_count := sql%rowcount;
2212:
2213: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6289: m.customer_id, m.customer_site_id) attrib_value_from, a.pricing_attr_value_from,
6290: m.customer_merge_id
6291: --changed for bug 3196900
6292: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6293: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A,OE_PRICE_ADJUSTMENTS P,OE_ORDER_HEADERS H
6294: WHERE
6295: a.pricing_context = 'CUSTOMER'
6296: and ((a.pricing_attribute in ('QUALIFIER_ATTRIBUTE11',
6297: 'QUALIFIER_ATTRIBUTE5',
6317: m.customer_merge_id
6318: --changed for bug 3196900
6319: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6320: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A , OE_PRICE_ADJUSTMENTS P,
6321: OE_ORDER_HEADERS H
6322: WHERE
6323: a.pricing_context = 'CUSTOMER'
6324: and ((a.pricing_attribute in ('QUALIFIER_ATTRIBUTE11',
6325: 'QUALIFIER_ATTRIBUTE5',
8466: ,l_set_of_books_id
8467: ,'XXX'
8468: ,l.last_update_date ----dbi_header_update_date_tab(i) bug 9349882,9558975
8469:
8470: FROM OE_ORDER_LINES_ALL l, oe_order_headers_all h
8471: WHERE l.header_id = dbi_header_header_id_tab(i)
8472: AND h.header_id = l.header_id
8473: AND h.booked_flag = 'Y';
8474: