[Home] [Help]
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)),
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'
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: