eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_BULK_VALUE_TO_ID dependencies on OE_HEADERS_IFACE_ALL

Line 387: UPDATE OE_HEADERS_IFACE_ALL c

383: -- ID column for order source is not populated but value column is.
384:
385: -- 2. ORDER_TYPE
386:
387: UPDATE OE_HEADERS_IFACE_ALL c
388: SET (ORDER_TYPE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
389: (SELECT b.ORDER_TYPE_ID,
390: DECODE(b.ORDER_TYPE_ID,NULL,'Y',NULL),
391: DECODE(b.ORDER_TYPE_ID,NULL,

Line 393: FROM OE_ORDER_TYPES_V b, OE_HEADERS_IFACE_ALL d

389: (SELECT b.ORDER_TYPE_ID,
390: DECODE(b.ORDER_TYPE_ID,NULL,'Y',NULL),
391: DECODE(b.ORDER_TYPE_ID,NULL,
392: c.ATTRIBUTE_STATUS||'002',c.ATTRIBUTE_STATUS)
393: FROM OE_ORDER_TYPES_V b, OE_HEADERS_IFACE_ALL d
394: WHERE d.order_type = b.name(+)
395: AND d.rowid = c.rowid
396: )
397: WHERE c.batch_id = p_batch_id

Line 404: UPDATE OE_HEADERS_IFACE_ALL c

400:
401:
402: -- 3. PRICE_LIST
403:
404: UPDATE OE_HEADERS_IFACE_ALL c
405: SET (price_list_id,ERROR_FLAG,ATTRIBUTE_STATUS)=
406: (SELECT b.LIST_HEADER_ID,
407: DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
408: DECODE(b.LIST_HEADER_ID,NULL,

Line 410: FROM qp_list_headers_vl b, OE_HEADERS_IFACE_ALL d

406: (SELECT b.LIST_HEADER_ID,
407: DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
408: DECODE(b.LIST_HEADER_ID,NULL,
409: c.ATTRIBUTE_STATUS||'003',c.ATTRIBUTE_STATUS)
410: FROM qp_list_headers_vl b, OE_HEADERS_IFACE_ALL d
411: WHERE d.price_list = b.name(+)
412: AND NVL(b.list_type_code,'PRL') IN ('PRL', 'AGR')
413: AND nvl(b.active_flag,'Y') = 'Y'
414: AND d.rowid = c.rowid

Line 423: UPDATE OE_HEADERS_IFACE_ALL c

419:
420:
421: -- 4. CONVERSION_TYPE
422:
423: UPDATE OE_HEADERS_IFACE_ALL c
424: SET (CONVERSION_TYPE_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
425: (SELECT b.CONVERSION_TYPE,
426: DECODE(b.CONVERSION_TYPE,NULL,'Y',NULL),
427: DECODE(b.CONVERSION_TYPE,NULL,

Line 429: FROM OE_GL_DAILY_CONVERSION_TYPES_V b, OE_HEADERS_IFACE_ALL d

425: (SELECT b.CONVERSION_TYPE,
426: DECODE(b.CONVERSION_TYPE,NULL,'Y',NULL),
427: DECODE(b.CONVERSION_TYPE,NULL,
428: c.ATTRIBUTE_STATUS||'004',c.ATTRIBUTE_STATUS)
429: FROM OE_GL_DAILY_CONVERSION_TYPES_V b, OE_HEADERS_IFACE_ALL d
430: WHERE d.CONVERSION_TYPE = b.USER_CONVERSION_TYPE(+)
431: AND d.rowid = c.rowid
432: )
433: WHERE c.batch_id = p_batch_id

Line 440: UPDATE OE_HEADERS_IFACE_ALL c

436:
437:
438: -- 5. SALESREP
439:
440: UPDATE OE_HEADERS_IFACE_ALL c
441: SET (SALESREP_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
442: (SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
443: DECODE(b.SALESREP_ID,NULL,'Y',NULL),
444: DECODE(b.SALESREP_ID,NULL,

Line 446: FROM RA_SALESREPS b, OE_HEADERS_IFACE_ALL d

442: (SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
443: DECODE(b.SALESREP_ID,NULL,'Y',NULL),
444: DECODE(b.SALESREP_ID,NULL,
445: c.ATTRIBUTE_STATUS||'005',c.ATTRIBUTE_STATUS)
446: FROM RA_SALESREPS b, OE_HEADERS_IFACE_ALL d
447: WHERE d.SALESREP = b.NAME(+)
448: AND sysdate between NVL(start_date_active,sysdate)
449: and NVL(end_date_active,sysdate)
450: AND d.rowid = c.rowid

Line 459: /* UPDATE OE_HEADERS_IFACE_ALL c

455:
456:
457: -- 6. TAX_EXEMPT_REASON VARCHAR2(30)
458: -- eBTax changes
459: /* UPDATE OE_HEADERS_IFACE_ALL c
460: SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
461: (SELECT b.LOOKUP_CODE,
462: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
463: DECODE(b.LOOKUP_CODE,NULL,

Line 465: FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d

461: (SELECT b.LOOKUP_CODE,
462: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
463: DECODE(b.LOOKUP_CODE,NULL,
464: c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
465: FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
466: WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
467: AND b.LOOKUP_TYPE(+) = 'TAX_REASON'
468: AND d.rowid = c.rowid
469: )

Line 474: UPDATE OE_HEADERS_IFACE_ALL c

470: WHERE c.batch_id = p_batch_id
471: AND c.TAX_EXEMPT_REASON_CODE IS NULL
472: AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
473:
474: UPDATE OE_HEADERS_IFACE_ALL c
475: SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
476: (SELECT b.LOOKUP_CODE,
477: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
478: DECODE(b.LOOKUP_CODE,NULL,

Line 480: FROM FND_LOOKUPS b, OE_HEADERS_IFACE_ALL d

476: (SELECT b.LOOKUP_CODE,
477: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
478: DECODE(b.LOOKUP_CODE,NULL,
479: c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
480: FROM FND_LOOKUPS b, OE_HEADERS_IFACE_ALL d
481: WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
482: AND b.LOOKUP_TYPE(+) = 'ZX_EXEMPTION_REASON_CODE'
483: AND d.rowid = c.rowid)
484: WHERE c.batch_id = p_batch_id

Line 492: UPDATE OE_HEADERS_IFACE_ALL c

488:
489:
490: -- 7. AGREEMENT VARCHAR2(50)
491:
492: UPDATE OE_HEADERS_IFACE_ALL c
493: SET (AGREEMENT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
494: (SELECT b.AGREEMENT_ID,
495: DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
496: DECODE(b.AGREEMENT_ID,NULL,

Line 498: FROM OE_AGREEMENTS_V b, OE_HEADERS_IFACE_ALL d

494: (SELECT b.AGREEMENT_ID,
495: DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
496: DECODE(b.AGREEMENT_ID,NULL,
497: c.ATTRIBUTE_STATUS||'007',c.ATTRIBUTE_STATUS)
498: FROM OE_AGREEMENTS_V b, OE_HEADERS_IFACE_ALL d
499: WHERE d.AGREEMENT = b.NAME(+)
500: AND sysdate between nvl(start_date_active, sysdate)
501: and nvl(end_date_active, sysdate)
502: AND d.rowid = c.rowid

Line 512: UPDATE OE_HEADERS_IFACE_ALL c

508:
509:
510: -- 8. INVOICING_RULE
511:
512: UPDATE OE_HEADERS_IFACE_ALL c
513: SET (INVOICING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
514: (SELECT b.RULE_ID,
515: DECODE(b.RULE_ID,NULL,'Y',NULL),
516: DECODE(b.RULE_ID,NULL,

Line 518: FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d

514: (SELECT b.RULE_ID,
515: DECODE(b.RULE_ID,NULL,'Y',NULL),
516: DECODE(b.RULE_ID,NULL,
517: c.ATTRIBUTE_STATUS||'008',c.ATTRIBUTE_STATUS)
518: FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
519: WHERE d.INVOICING_RULE = b.NAME(+)
520: AND b.STATUS(+) = 'A'
521: AND b.TYPE(+) = 'I'
522: AND d.rowid = c.rowid

Line 530: UPDATE OE_HEADERS_IFACE_ALL c

526: AND c.INVOICING_RULE IS NOT NULL;
527:
528: -- 9. ACCOUNTING_RULE VARCHAR2(30)
529:
530: UPDATE OE_HEADERS_IFACE_ALL c
531: SET (ACCOUNTING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
532: (SELECT b.RULE_ID,
533: DECODE(b.RULE_ID,NULL,'Y',NULL),
534: DECODE(b.RULE_ID,NULL,

Line 536: FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d

532: (SELECT b.RULE_ID,
533: DECODE(b.RULE_ID,NULL,'Y',NULL),
534: DECODE(b.RULE_ID,NULL,
535: c.ATTRIBUTE_STATUS||'009',c.ATTRIBUTE_STATUS)
536: FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
537: WHERE d.ACCOUNTING_RULE = b.NAME(+)
538: AND b.STATUS(+) = 'A'
539: -- AND b.TYPE(+) = 'A' --we now allow variable accounting rules
540: AND d.rowid = c.rowid

Line 548: UPDATE OE_HEADERS_IFACE_ALL c

544: AND c.ACCOUNTING_RULE IS NOT NULL;
545:
546: -- 10 PAYMENT_TERM VARCHAR2(30)
547:
548: UPDATE OE_HEADERS_IFACE_ALL c
549: SET (PAYMENT_TERM_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
550: (SELECT b.TERM_ID,
551: DECODE(b.TERM_ID,NULL,'Y',NULL),
552: DECODE(b.TERM_ID,NULL,

Line 554: FROM OE_RA_TERMS_V b, OE_HEADERS_IFACE_ALL d

550: (SELECT b.TERM_ID,
551: DECODE(b.TERM_ID,NULL,'Y',NULL),
552: DECODE(b.TERM_ID,NULL,
553: c.ATTRIBUTE_STATUS||'010',c.ATTRIBUTE_STATUS)
554: FROM OE_RA_TERMS_V b, OE_HEADERS_IFACE_ALL d
555: WHERE d.PAYMENT_TERM = b.NAME(+)
556: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
557: AND NVL(END_DATE_ACTIVE, SYSDATE)
558: AND d.rowid = c.rowid

Line 567: UPDATE OE_HEADERS_IFACE_ALL c

563:
564:
565: -- 11. FREIGHT_TERMS VARCHAR2(30)
566:
567: UPDATE OE_HEADERS_IFACE_ALL c
568: SET (FREIGHT_TERMS_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
569: (SELECT b.LOOKUP_CODE,
570: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
571: DECODE(b.LOOKUP_CODE,NULL,

Line 573: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d

569: (SELECT b.LOOKUP_CODE,
570: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
571: DECODE(b.LOOKUP_CODE,NULL,
572: c.ATTRIBUTE_STATUS||'011',c.ATTRIBUTE_STATUS)
573: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
574: WHERE d.FREIGHT_TERMS = b.MEANING(+)
575: AND b.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
576: AND b.enabled_flag(+) = 'Y'
577: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)

Line 588: UPDATE OE_HEADERS_IFACE_ALL c

584:
585:
586: -- 12. FOB_POINT VARCHAR2(30)
587:
588: UPDATE OE_HEADERS_IFACE_ALL c
589: SET (FOB_POINT_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
590: (SELECT b.LOOKUP_CODE,
591: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
592: DECODE(b.LOOKUP_CODE,NULL,

Line 594: FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d

590: (SELECT b.LOOKUP_CODE,
591: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
592: DECODE(b.LOOKUP_CODE,NULL,
593: c.ATTRIBUTE_STATUS||'012',c.ATTRIBUTE_STATUS)
594: FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
595: WHERE d.FOB_POINT = b.MEANING(+)
596: AND b.LOOKUP_TYPE(+) = 'FOB'
597: AND b.enabled_flag(+) = 'Y'
598: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)

Line 616: UPDATE OE_HEADERS_IFACE_ALL c

612:
613: --bug 5620045
614: --Catch subqry_too_many_rows exception
615: BEGIN
616: UPDATE OE_HEADERS_IFACE_ALL c
617: SET (SOLD_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
618: (SELECT b.CUST_ACCOUNT_ID,
619: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
620: DECODE(b.CUST_ACCOUNT_ID,NULL,

Line 622: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d

618: (SELECT b.CUST_ACCOUNT_ID,
619: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
620: DECODE(b.CUST_ACCOUNT_ID,NULL,
621: c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
622: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
623: WHERE d.CUSTOMER_NUMBER IS NOT NULL
624: AND d.CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
625: AND b.STATUS(+) = 'A'
626: AND d.rowid = c.rowid

Line 632: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d

628: SELECT b.CUST_ACCOUNT_ID,
629: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
630: DECODE(b.CUST_ACCOUNT_ID,NULL,
631: c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
632: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
633: WHERE d.CUSTOMER_NUMBER IS NULL
634: AND d.SOLD_TO_ORG = e.PARTY_NAME(+)
635: AND b.STATUS(+) = 'A'
636: AND b.PARTY_ID(+) = e.PARTY_ID

Line 645: UPDATE OE_HEADERS_IFACE_ALL c

641: AND (c.SOLD_TO_ORG IS NOT NULL OR
642: c.CUSTOMER_NUMBER IS NOT NULL);
643: EXCEPTION
644: WHEN subqry_too_many_rows THEN
645: UPDATE OE_HEADERS_IFACE_ALL c
646: SET ERROR_FLAG = 'Y',
647: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'013'
648: WHERE c.batch_id = p_batch_id
649: AND c.SOLD_TO_ORG_ID IS NULL

Line 676: UPDATE OE_HEADERS_IFACE_ALL c

672: -- SHIP_TO_CUSTOMER
673: --bug 5620045
674: --catch subqry_too_many_rows
675: BEGIN
676: UPDATE OE_HEADERS_IFACE_ALL c
677: SET (SHIP_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
678: (SELECT b.CUST_ACCOUNT_ID,
679: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
680: DECODE(b.CUST_ACCOUNT_ID,NULL,

Line 682: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d

678: (SELECT b.CUST_ACCOUNT_ID,
679: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
680: DECODE(b.CUST_ACCOUNT_ID,NULL,
681: d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
682: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
683: WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL
684: AND d.SHIP_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
685: AND b.STATUS(+) = 'A'
686: AND d.rowid = c.rowid

Line 692: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d

688: SELECT b.CUST_ACCOUNT_ID,
689: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
690: DECODE(b.CUST_ACCOUNT_ID,NULL,
691: d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
692: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
693: WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NULL
694: AND d.SHIP_TO_CUSTOMER = e.PARTY_NAME(+)
695: AND b.STATUS(+) = 'A'
696: AND b.PARTY_ID(+) = e.PARTY_ID

Line 705: UPDATE OE_HEADERS_IFACE_ALL c

701: AND (c.SHIP_TO_CUSTOMER IS NOT NULL OR
702: c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);
703: EXCEPTION
704: WHEN subqry_too_many_rows THEN
705: UPDATE OE_HEADERS_IFACE_ALL c
706: SET ERROR_FLAG = 'Y',
707: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'014'
708: WHERE c.batch_id = p_batch_id
709: AND c.SHIP_TO_CUSTOMER_ID IS NULL

Line 717: UPDATE OE_HEADERS_IFACE_ALL c

713: -- INVOICE_TO_CUSTOMER
714: --bug 5620045
715: --catch subqry_too_many_rows
716: BEGIN
717: UPDATE OE_HEADERS_IFACE_ALL c
718: SET (INVOICE_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
719: (SELECT b.CUST_ACCOUNT_ID,
720: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
721: DECODE(b.CUST_ACCOUNT_ID,NULL,

Line 723: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d

719: (SELECT b.CUST_ACCOUNT_ID,
720: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
721: DECODE(b.CUST_ACCOUNT_ID,NULL,
722: d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
723: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
724: WHERE d.INVOICE_CUSTOMER_NUMBER IS NOT NULL
725: AND d.INVOICE_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
726: AND b.STATUS(+) = 'A'
727: AND d.rowid = c.rowid

Line 733: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d

729: SELECT b.CUST_ACCOUNT_ID,
730: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
731: DECODE(b.CUST_ACCOUNT_ID,NULL,
732: d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
733: FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
734: WHERE d.INVOICE_CUSTOMER_NUMBER IS NULL
735: AND d.INVOICE_CUSTOMER = e.PARTY_NAME(+)
736: AND b.STATUS(+) = 'A'
737: AND b.PARTY_ID(+) = e.PARTY_ID

Line 746: UPDATE OE_HEADERS_IFACE_ALL c

742: AND (c.INVOICE_CUSTOMER IS NOT NULL OR
743: c.INVOICE_CUSTOMER_NUMBER IS NOT NULL);
744: EXCEPTION
745: WHEN subqry_too_many_rows THEN
746: UPDATE OE_HEADERS_IFACE_ALL c
747: SET ERROR_FLAG = 'Y',
748: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
749: WHERE c.batch_id = p_batch_id
750: AND c.INVOICE_CUSTOMER_ID IS NULL

Line 758: UPDATE OE_HEADERS_IFACE_ALL c

754: END IF;
755:
756: -- 14. SHIP_TO_ORG
757:
758: UPDATE OE_HEADERS_IFACE_ALL c
759: SET (SHIP_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
760: (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
761: DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
762: DECODE(b.ORGANIZATION_ID,NULL,

Line 764: FROM OE_SHIP_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d

760: (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
761: DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
762: DECODE(b.ORGANIZATION_ID,NULL,
763: d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
764: FROM OE_SHIP_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
765: WHERE d.ship_to_address1 = ADDRESS_LINE_1(+)
766: AND nvl(d.ship_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
767: AND nvl(d.ship_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
768: --AND nvl(d.ship_to_address4,'NIL') = ADDRESS_LINE_4

Line 788: UPDATE OE_HEADERS_IFACE_ALL c

784: );
785:
786: -- 15. INVOICE_TO_ORG
787:
788: UPDATE OE_HEADERS_IFACE_ALL c
789: SET (INVOICE_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
790: (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
791: DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
792: DECODE(b.ORGANIZATION_ID,NULL,

Line 794: FROM OE_INVOICE_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d

790: (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
791: DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
792: DECODE(b.ORGANIZATION_ID,NULL,
793: d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
794: FROM OE_INVOICE_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
795: WHERE d.invoice_address1 = ADDRESS_LINE_1(+)
796: AND nvl(d.invoice_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
797: AND nvl(d.invoice_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
798: --AND nvl(d.invoice_address4,'NIL') = ADDRESS_LINE_4

Line 821: UPDATE OE_HEADERS_IFACE_ALL c

817: -- DELIVER_TO_ORG No mapping columns in the interface table
818:
819: -- 16. SOLD_TO_CONTACT VARCHAR2(30)
820:
821: UPDATE OE_HEADERS_IFACE_ALL c
822: SET (SOLD_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
823: (SELECT DISTINCT b.CONTACT_ID,
824: DECODE(b.CONTACT_ID,NULL,'Y',NULL),
825: DECODE(b.CONTACT_ID,NULL,

Line 839: UPDATE OE_HEADERS_IFACE_ALL c

835:
836:
837: -- 17. SHIP_TO_CONTACT VARCHAR2(30)
838:
839: UPDATE OE_HEADERS_IFACE_ALL c
840: SET (SHIP_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
841: (SELECT Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),
842: DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,'Y',NULL),
843: DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,

Line 854: UPDATE OE_HEADERS_IFACE_ALL c

850:
851:
852: -- 18. INVOICE_TO_CONTACT VARCHAR2(30)
853:
854: UPDATE OE_HEADERS_IFACE_ALL c
855: SET (INVOICE_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
856: (SELECT Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),
857: DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,'Y',NULL),
858: DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,

Line 869: UPDATE OE_HEADERS_IFACE_ALL c

865:
866:
867: -- 19. DELIVER_TO_CONTACT VARCHAR2(30)
868:
869: UPDATE OE_HEADERS_IFACE_ALL c
870: SET (DELIVER_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
871: (SELECT Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),
872: DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,'Y',NULL),
873: DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,

Line 883: UPDATE OE_HEADERS_IFACE_ALL c

879: AND c.DELIVER_TO_CONTACT IS NOT NULL;
880:
881:
882: -- SHIPPING_METHOD_CODE VARCHAR2(30)
883: UPDATE OE_HEADERS_IFACE_ALL c
884: SET (SHIPPING_METHOD_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
885: (SELECT b.LOOKUP_CODE,
886: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
887: DECODE(b.LOOKUP_CODE,NULL,

Line 889: FROM OE_SHIP_METHODS_V b, OE_HEADERS_IFACE_ALL d

885: (SELECT b.LOOKUP_CODE,
886: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
887: DECODE(b.LOOKUP_CODE,NULL,
888: c.ATTRIBUTE_STATUS||'024',c.ATTRIBUTE_STATUS)
889: FROM OE_SHIP_METHODS_V b, OE_HEADERS_IFACE_ALL d
890: WHERE d.SHIPPING_METHOD = b.MEANING(+)
891: AND b.enabled_flag(+) = 'Y'
892: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE(+), SYSDATE)
893: AND NVL(END_DATE_ACTIVE(+), SYSDATE)

Line 904: UPDATE OE_HEADERS_IFACE_ALL c

900: -- FREIGHT_CARRIER_CODE: value column does not exist on interface tables
901:
902: -- SALES_CHANNEL_CODE
903:
904: UPDATE OE_HEADERS_IFACE_ALL c
905: SET (SALES_CHANNEL_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
906: (SELECT b.LOOKUP_CODE,
907: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
908: DECODE(b.LOOKUP_CODE,NULL,

Line 910: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d

906: (SELECT b.LOOKUP_CODE,
907: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
908: DECODE(b.LOOKUP_CODE,NULL,
909: c.ATTRIBUTE_STATUS||'022',c.ATTRIBUTE_STATUS)
910: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
911: WHERE d.SALES_CHANNEL = b.MEANING(+)
912: AND b.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
913: AND b.enabled_flag(+) = 'Y'
914: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)

Line 924: UPDATE OE_HEADERS_IFACE_ALL c

920: AND c.SALES_CHANNEL IS NOT NULL;
921:
922: -- SHIPMENT_PRIORITY_CODE
923:
924: UPDATE OE_HEADERS_IFACE_ALL c
925: SET (SHIPMENT_PRIORITY_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
926: (SELECT b.LOOKUP_CODE,
927: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
928: DECODE(b.LOOKUP_CODE,NULL,

Line 930: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d

926: (SELECT b.LOOKUP_CODE,
927: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
928: DECODE(b.LOOKUP_CODE,NULL,
929: c.ATTRIBUTE_STATUS||'023',c.ATTRIBUTE_STATUS)
930: FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
931: WHERE d.SHIPMENT_PRIORITY = b.MEANING(+)
932: AND b.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
933: AND b.enabled_flag(+) = 'Y'
934: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)

Line 946: UPDATE OE_HEADERS_IFACE_ALL c

942: -- Sold_to_site_use_id
943: --abghosh
944:
945: IF OE_CODE_CONTROL.Code_Release_Level >='110510' THEN
946: UPDATE OE_HEADERS_IFACE_ALL c
947: SET (SOLD_TO_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
948: (SELECT SITE.SITE_USE_ID,
949: DECODE(SITE.SITE_USE_ID,null,'Y',null),
950: DECODE(SITE.SITE_USE_ID,null,d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)

Line 956: OE_HEADERS_IFACE_ALL d

952: FROM HZ_CUST_SITE_USES_ALL SITE,
953: HZ_PARTY_SITES PARTY_SITE,
954: HZ_LOCATIONS LOC,
955: HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
956: OE_HEADERS_IFACE_ALL d
957:
958: WHERE SITE.SITE_USE_CODE ='SOLD_TO'
959: AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
960: AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID

Line 987: UPDATE OE_HEADERS_IFACE_ALL c

983: --{ Bug 5054618
984: --End customer changes for HVOP
985: --bug 5620045 catch subqry_too_many_rows
986: BEGIN
987: UPDATE OE_HEADERS_IFACE_ALL c
988: SET (END_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
989: (SELECT b.CUST_ACCOUNT_ID,
990: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
991: DECODE(b.CUST_ACCOUNT_ID,NULL,

Line 993: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d

989: (SELECT b.CUST_ACCOUNT_ID,
990: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
991: DECODE(b.CUST_ACCOUNT_ID,NULL,
992: d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
993: FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
994: WHERE d.END_CUSTOMER_NUMBER IS NOT NULL AND d.END_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
995: AND b.STATUS(+) = 'A' AND d.rowid = c.rowid
996: UNION ALL
997: SELECT b.CUST_ACCOUNT_ID,

Line 1000: d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS) FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d

996: UNION ALL
997: SELECT b.CUST_ACCOUNT_ID,
998: DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
999: DECODE(b.CUST_ACCOUNT_ID,NULL,
1000: d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS) FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
1001: WHERE d.END_CUSTOMER_NUMBER IS NULL AND d.END_CUSTOMER_NAME = e.PARTY_NAME(+)
1002: AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
1003: AND d.rowid = c.rowid)
1004: WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_ID IS NULL AND

Line 1008: UPDATE OE_HEADERS_IFACE_ALL c

1004: WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_ID IS NULL AND
1005: (c.END_CUSTOMER_NAME IS NOT NULL OR c.END_CUSTOMER_NUMBER IS NOT NULL);
1006: EXCEPTION
1007: WHEN subqry_too_many_rows THEN
1008: UPDATE OE_HEADERS_IFACE_ALL c
1009: SET ERROR_FLAG = 'Y',
1010: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
1011: WHERE c.batch_id = p_batch_id
1012: AND c.END_CUSTOMER_ID IS NULL

Line 1018: UPDATE OE_HEADERS_IFACE_ALL c

1014: END;
1015:
1016:
1017:
1018: UPDATE OE_HEADERS_IFACE_ALL c
1019: SET (END_CUSTOMER_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1020: (SELECT END_CUSTOMER_SITE(c.end_customer_address1,c.end_customer_address2,c.end_customer_address3,c.end_customer_address4,
1021: c.end_customer_location, NULL,c.end_customer_id,c.end_customer_city,
1022: c.end_customer_state,c.end_customer_postal_code,c.end_customer_country,NULL),

Line 1049: UPDATE OE_HEADERS_IFACE_ALL c

1045: OR c.END_CUSTOMER_ADDRESS3 IS NOT NULL
1046: OR c.END_CUSTOMER_ADDRESS4 IS NOT NULL);
1047:
1048:
1049: UPDATE OE_HEADERS_IFACE_ALL c
1050: SET (END_CUSTOMER_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1051: (SELECT Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),
1052: DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,'Y',NULL),
1053: DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)

Line 1058: UPDATE OE_HEADERS_IFACE_ALL c

1054: FROM DUAL) WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_CONTACT_ID IS NULL AND c.END_CUSTOMER_CONTACT IS NOT NULL;
1055:
1056: --Bug 5054618}
1057:
1058: UPDATE OE_HEADERS_IFACE_ALL c
1059: SET (IB_OWNER_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1060: (SELECT b.LOOKUP_CODE,
1061: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1062: DECODE(b.LOOKUP_CODE,NULL,

Line 1071: OE_HEADERS_IFACE_ALL d

1067: where lookup_type In ('ITEM_OWNER', 'ONT_INSTALL_BASE')
1068: and enabled_flag = 'Y'
1069: and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1070: AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1071: OE_HEADERS_IFACE_ALL d
1072: WHERE d.IB_OWNER= b.MEANING(+)
1073: --AND ( b.LOOKUP_TYPE(+) = 'ITEM_OWNER'
1074: --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1075: --AND b.enabled_flag(+) = 'Y'

Line 1084: UPDATE OE_HEADERS_IFACE_ALL c

1080: )
1081: WHERE c.batch_id = p_batch_id
1082: AND c.IB_OWNER_CODE IS NULL AND c.IB_OWNER IS NOT NULL;
1083:
1084: UPDATE OE_HEADERS_IFACE_ALL c
1085: SET (IB_INSTALLED_AT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1086: (SELECT b.LOOKUP_CODE,
1087: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1088: DECODE(b.LOOKUP_CODE,NULL,

Line 1097: OE_HEADERS_IFACE_ALL d

1093: where lookup_type In ('ITEM_INSTALL_LOCATION', 'ONT_INSTALL_BASE')
1094: and enabled_flag = 'Y'
1095: and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1096: AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1097: OE_HEADERS_IFACE_ALL d
1098: WHERE d.IB_INSTALLED_AT_LOCATION = b.MEANING(+)
1099: --AND ( b.LOOKUP_TYPE(+) = 'ITEM_INSTALL_LOCATION'
1100: --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1101: --AND b.enabled_flag(+) = 'Y'

Line 1111: UPDATE OE_HEADERS_IFACE_ALL c

1107: WHERE c.batch_id = p_batch_id
1108: AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
1109: AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
1110:
1111: UPDATE OE_HEADERS_IFACE_ALL c
1112: SET (IB_CURRENT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1113: (SELECT b.LOOKUP_CODE,
1114: DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1115: DECODE(b.LOOKUP_CODE,NULL,

Line 1124: OE_HEADERS_IFACE_ALL d

1120: where lookup_type In ('ITEM_CURRENT_LOCATION', 'ONT_INSTALL_BASE')
1121: and enabled_flag = 'Y'
1122: and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1123: AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1124: OE_HEADERS_IFACE_ALL d
1125: WHERE d.IB_CURRENT_LOCATION = b.MEANING(+)
1126: --AND ( b.LOOKUP_TYPE(+) = 'ITEM_CURRENT_LOCATION'
1127: --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1128: --AND b.enabled_flag(+) = 'Y'

Line 1203: FROM OE_HEADERS_IFACE_ALL

1199: AND d.rowid = c.rowid
1200: )
1201: WHERE (order_source_id, orig_sys_document_ref) IN
1202: ( SELECT order_source_id, orig_sys_document_ref
1203: FROM OE_HEADERS_IFACE_ALL
1204: WHERE batch_id = p_batch_id)
1205: AND c.LINE_TYPE_ID IS NULL
1206: AND c.LINE_TYPE IS NOT NULL;
1207:

Line 1224: FROM OE_HEADERS_IFACE_ALL

1220: AND d.rowid = c.rowid
1221: )
1222: WHERE (order_source_id, orig_sys_document_ref) IN
1223: ( SELECT order_source_id, orig_sys_document_ref
1224: FROM OE_HEADERS_IFACE_ALL
1225: WHERE batch_id = p_batch_id)
1226: AND c.price_list_id IS NULL
1227: AND c.price_list IS NOT NULL;
1228:

Line 1246: FROM OE_HEADERS_IFACE_ALL

1242: AND d.rowid = c.rowid
1243: )
1244: WHERE (order_source_id, orig_sys_document_ref) IN
1245: ( SELECT order_source_id, orig_sys_document_ref
1246: FROM OE_HEADERS_IFACE_ALL
1247: WHERE batch_id = p_batch_id)
1248: AND c.SALESREP_ID IS NULL
1249: AND c.SALESREP IS NOT NULL;
1250:

Line 1271: FROM OE_HEADERS_IFACE_ALL

1267: AND d.rowid = c.rowid
1268: )
1269: WHERE (order_source_id, orig_sys_document_ref) IN
1270: ( SELECT order_source_id, orig_sys_document_ref
1271: FROM OE_HEADERS_IFACE_ALL
1272: WHERE batch_id = p_batch_id)
1273: AND c.TAX_EXEMPT_REASON_CODE IS NULL
1274: AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
1275:

Line 1291: FROM OE_HEADERS_IFACE_ALL

1287: AND NVL(END_DATE_ACTIVE, SYSDATE)
1288: AND d.rowid = c.rowid)
1289: WHERE (order_source_id, orig_sys_document_ref) IN
1290: (SELECT order_source_id, orig_sys_document_ref
1291: FROM OE_HEADERS_IFACE_ALL
1292: WHERE batch_id = p_batch_id)
1293: AND c.TAX_EXEMPT_REASON_CODE IS NULL
1294: AND c.TAX_EXEMPT_REASON IS NOT NULL;
1295:

Line 1314: FROM OE_HEADERS_IFACE_ALL

1310: AND d.rowid = c.rowid
1311: )
1312: WHERE (order_source_id, orig_sys_document_ref) IN
1313: ( SELECT order_source_id, orig_sys_document_ref
1314: FROM OE_HEADERS_IFACE_ALL
1315: WHERE batch_id = p_batch_id)
1316: AND c.AGREEMENT_ID IS NULL
1317: AND c.AGREEMENT IS NOT NULL;
1318:

Line 1337: FROM OE_HEADERS_IFACE_ALL

1333: AND d.rowid = c.rowid
1334: )
1335: WHERE (order_source_id, orig_sys_document_ref) IN
1336: ( SELECT order_source_id, orig_sys_document_ref
1337: FROM OE_HEADERS_IFACE_ALL
1338: WHERE batch_id = p_batch_id)
1339: AND c.INVOICING_RULE_ID IS NULL
1340: AND c.INVOICING_RULE IS NOT NULL;
1341:

Line 1358: FROM OE_HEADERS_IFACE_ALL

1354: AND d.rowid = c.rowid
1355: )
1356: WHERE (order_source_id, orig_sys_document_ref) IN
1357: ( SELECT order_source_id, orig_sys_document_ref
1358: FROM OE_HEADERS_IFACE_ALL
1359: WHERE batch_id = p_batch_id)
1360: AND c.ACCOUNTING_RULE_ID IS NULL
1361: AND c.ACCOUNTING_RULE IS NOT NULL;
1362:

Line 1379: FROM OE_HEADERS_IFACE_ALL

1375: AND d.rowid = c.rowid
1376: )
1377: WHERE (order_source_id, orig_sys_document_ref) IN
1378: ( SELECT order_source_id, orig_sys_document_ref
1379: FROM OE_HEADERS_IFACE_ALL
1380: WHERE batch_id = p_batch_id)
1381: AND c.PAYMENT_TERM_ID IS NULL
1382: AND c.PAYMENT_TERM IS NOT NULL;
1383:

Line 1403: FROM OE_HEADERS_IFACE_ALL

1399: AND d.rowid = c.rowid
1400: )
1401: WHERE (order_source_id, orig_sys_document_ref) IN
1402: ( SELECT order_source_id, orig_sys_document_ref
1403: FROM OE_HEADERS_IFACE_ALL
1404: WHERE batch_id = p_batch_id)
1405: AND c.FREIGHT_TERMS_CODE IS NULL
1406: AND c.FREIGHT_TERMS IS NOT NULL;
1407:

Line 1428: FROM OE_HEADERS_IFACE_ALL

1424: AND d.rowid = c.rowid
1425: )
1426: WHERE (order_source_id, orig_sys_document_ref) IN
1427: ( SELECT order_source_id, orig_sys_document_ref
1428: FROM OE_HEADERS_IFACE_ALL
1429: WHERE batch_id = p_batch_id)
1430: AND c.FOB_POINT_CODE IS NULL
1431: AND c.FOB_POINT IS NOT NULL;
1432:

Line 1438: (SELECT d.SOLD_TO_ORG_ID FROM OE_HEADERS_IFACE_ALL d, OE_LINES_IFACE_ALL e

1434: -- SOLD_TO_ORG -- Required as it is used later for invoice
1435: -- and ship to org
1436: UPDATE OE_LINES_IFACE_ALL c
1437: SET SOLD_TO_ORG_ID =
1438: (SELECT d.SOLD_TO_ORG_ID FROM OE_HEADERS_IFACE_ALL d, OE_LINES_IFACE_ALL e
1439: WHERE d.order_source_id = e.order_source_id
1440: AND d.orig_sys_document_ref = e.orig_sys_document_ref
1441: AND batch_id = p_batch_id
1442: AND d.sold_to_org_id IS NOT NULL

Line 1447: FROM OE_HEADERS_IFACE_ALL

1443: AND c.rowid = e.rowid
1444: )
1445: WHERE (order_source_id, orig_sys_document_ref) IN
1446: ( SELECT order_source_id, orig_sys_document_ref
1447: FROM OE_HEADERS_IFACE_ALL
1448: WHERE batch_id = p_batch_id
1449: )
1450: AND c.SOLD_TO_ORG_ID IS NULL;
1451:

Line 1491: FROM OE_HEADERS_IFACE_ALL

1487: AND d.rowid = c.rowid
1488: )
1489: WHERE (order_source_id, orig_sys_document_ref) IN
1490: ( SELECT order_source_id, orig_sys_document_ref
1491: FROM OE_HEADERS_IFACE_ALL
1492: WHERE batch_id = p_batch_id)
1493: AND c.SHIP_TO_CUSTOMER_ID IS NULL
1494: AND (c.SHIP_TO_CUSTOMER_NAME IS NOT NULL OR
1495: c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);

Line 1505: FROM OE_HEADERS_IFACE_ALL

1501: WHERE c.SHIP_TO_CUSTOMER_ID IS NULL
1502: AND c.SHIP_TO_CUSTOMER_NAME IS NOT NULL
1503: AND (order_source_id, orig_sys_document_ref) IN
1504: ( SELECT order_source_id, orig_sys_document_ref
1505: FROM OE_HEADERS_IFACE_ALL
1506: WHERE batch_id = p_batch_id) ;
1507:
1508: END;
1509:

Line 1538: FROM OE_HEADERS_IFACE_ALL

1534: AND d.rowid = c.rowid
1535: )
1536: WHERE (order_source_id, orig_sys_document_ref) IN
1537: ( SELECT order_source_id, orig_sys_document_ref
1538: FROM OE_HEADERS_IFACE_ALL
1539: WHERE batch_id = p_batch_id)
1540: AND c.INVOICE_TO_CUSTOMER_ID IS NULL
1541: AND (c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL OR
1542: c.INVOICE_TO_CUSTOMER_NUMBER IS NOT NULL);

Line 1550: FROM OE_HEADERS_IFACE_ALL

1546: SET ERROR_FLAG = 'Y',
1547: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
1548: WHERE (order_source_id, orig_sys_document_ref) IN
1549: ( SELECT order_source_id, orig_sys_document_ref
1550: FROM OE_HEADERS_IFACE_ALL
1551: WHERE batch_id = p_batch_id)
1552: AND c.INVOICE_TO_CUSTOMER_ID IS NULL
1553: AND c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL ;
1554: END;

Line 1581: FROM OE_HEADERS_IFACE_ALL

1577: AND d.rowid = c.rowid
1578: )
1579: WHERE (order_source_id, orig_sys_document_ref) IN
1580: ( SELECT order_source_id, orig_sys_document_ref
1581: FROM OE_HEADERS_IFACE_ALL
1582: WHERE batch_id = p_batch_id)
1583: AND c.SHIP_TO_ORG_ID IS NULL
1584: AND (c.SHIP_TO_ADDRESS1 IS NOT NULL
1585: OR c.ship_to_address2 IS NOT NULL

Line 1614: FROM OE_HEADERS_IFACE_ALL

1610: AND d.rowid = c.rowid
1611: )
1612: WHERE (order_source_id, orig_sys_document_ref) IN
1613: ( SELECT order_source_id, orig_sys_document_ref
1614: FROM OE_HEADERS_IFACE_ALL
1615: WHERE batch_id = p_batch_id)
1616: AND c.INVOICE_TO_ORG_ID IS NULL
1617: AND (c.invoice_to_address1 IS NOT NULL
1618: OR c.invoice_to_address2 IS NOT NULL

Line 1637: FROM OE_HEADERS_IFACE_ALL

1633: FROM DUAL
1634: )
1635: WHERE (order_source_id, orig_sys_document_ref) IN
1636: ( SELECT order_source_id, orig_sys_document_ref
1637: FROM OE_HEADERS_IFACE_ALL
1638: WHERE batch_id = p_batch_id)
1639: AND c.SHIP_TO_CONTACT_ID IS NULL
1640: AND c.SHIP_TO_CONTACT IS NOT NULL;
1641:

Line 1655: FROM OE_HEADERS_IFACE_ALL

1651: FROM DUAL
1652: )
1653: WHERE (order_source_id, orig_sys_document_ref) IN
1654: ( SELECT order_source_id, orig_sys_document_ref
1655: FROM OE_HEADERS_IFACE_ALL
1656: WHERE batch_id = p_batch_id)
1657: AND c.INVOICE_TO_CONTACT_ID IS NULL
1658: AND c.INVOICE_TO_CONTACT IS NOT NULL;
1659:

Line 1673: FROM OE_HEADERS_IFACE_ALL

1669: FROM DUAL
1670: )
1671: WHERE (order_source_id, orig_sys_document_ref) IN
1672: ( SELECT order_source_id, orig_sys_document_ref
1673: FROM OE_HEADERS_IFACE_ALL
1674: WHERE batch_id = p_batch_id)
1675: AND c.DELIVER_TO_CONTACT_ID IS NULL
1676: AND c.DELIVER_TO_CONTACT IS NOT NULL;
1677:

Line 1699: FROM OE_HEADERS_IFACE_ALL

1695: AND d.rowid = c.rowid
1696: )
1697: WHERE (order_source_id, orig_sys_document_ref) IN
1698: ( SELECT order_source_id, orig_sys_document_ref
1699: FROM OE_HEADERS_IFACE_ALL
1700: WHERE batch_id = p_batch_id)
1701: AND c.SHIPMENT_PRIORITY_CODE IS NULL
1702: AND c.SHIPMENT_PRIORITY IS NOT NULL;
1703:

Line 1723: FROM OE_HEADERS_IFACE_ALL

1719: AND d.rowid = c.rowid
1720: )
1721: WHERE (order_source_id, orig_sys_document_ref) IN
1722: ( SELECT order_source_id, orig_sys_document_ref
1723: FROM OE_HEADERS_IFACE_ALL
1724: WHERE batch_id = p_batch_id)
1725: AND c.SHIPPING_METHOD_CODE IS NULL
1726: AND c.SHIPPING_METHOD IS NOT NULL;
1727:

Line 1783: FROM OE_HEADERS_IFACE_ALL

1779: AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
1780: AND d.rowid = c.rowid)
1781: WHERE (order_source_id, orig_sys_document_ref) IN
1782: ( SELECT order_source_id, orig_sys_document_ref
1783: FROM OE_HEADERS_IFACE_ALL
1784: WHERE batch_id = p_batch_id)
1785: AND c.END_CUSTOMER_ID IS NULL
1786: AND (c.END_CUSTOMER_NAME IS NOT NULL
1787: OR c.END_CUSTOMER_NUMBER IS NOT NULL);

Line 1795: FROM OE_HEADERS_IFACE_ALL

1791: SET ERROR_FLAG = 'Y',
1792: ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
1793: WHERE (order_source_id, orig_sys_document_ref) IN
1794: ( SELECT order_source_id, orig_sys_document_ref
1795: FROM OE_HEADERS_IFACE_ALL
1796: WHERE batch_id = p_batch_id)
1797: AND c.END_CUSTOMER_ID IS NULL
1798: AND c.END_CUSTOMER_NAME IS NOT NULL ;
1799:

Line 1817: FROM OE_HEADERS_IFACE_ALL

1813: c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)
1814: FROM DUAL)
1815: WHERE (order_source_id, orig_sys_document_ref) IN
1816: ( SELECT order_source_id, orig_sys_document_ref
1817: FROM OE_HEADERS_IFACE_ALL
1818: WHERE batch_id = p_batch_id)
1819: AND c.END_CUSTOMER_CONTACT_ID IS NULL
1820: AND c.END_CUSTOMER_CONTACT IS NOT NULL;
1821:

Line 1853: FROM OE_HEADERS_IFACE_ALL

1849: NULL,c.ATTRIBUTE_STATUS||'027',c.ATTRIBUTE_STATUS)
1850: FROM DUAL)
1851: WHERE (order_source_id, orig_sys_document_ref) IN
1852: ( SELECT order_source_id, orig_sys_document_ref
1853: FROM OE_HEADERS_IFACE_ALL
1854: WHERE batch_id = p_batch_id)
1855: AND c.END_CUSTOMER_SITE_USE_ID IS NULL AND c.END_CUSTOMER_ID IS NOT NULL
1856: AND (c.END_CUSTOMER_ADDRESS1 IS NOT NULL
1857: OR c.END_CUSTOMER_ADDRESS2 IS NOT NULL

Line 1889: FROM OE_HEADERS_IFACE_ALL

1885: AND rownum = 1
1886: )
1887: WHERE (order_source_id, orig_sys_document_ref) IN
1888: ( SELECT order_source_id, orig_sys_document_ref
1889: FROM OE_HEADERS_IFACE_ALL
1890: WHERE batch_id = p_batch_id)
1891: AND c.IB_OWNER_CODE IS NULL
1892: AND c.IB_OWNER IS NOT NULL;
1893:

Line 1920: FROM OE_HEADERS_IFACE_ALL

1916: AND rownum = 1
1917: )
1918: WHERE (order_source_id, orig_sys_document_ref) IN
1919: ( SELECT order_source_id, orig_sys_document_ref
1920: FROM OE_HEADERS_IFACE_ALL
1921: WHERE batch_id = p_batch_id)
1922: AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
1923: AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
1924:

Line 1950: FROM OE_HEADERS_IFACE_ALL

1946: AND rownum = 1
1947: )
1948: WHERE (order_source_id, orig_sys_document_ref) IN
1949: ( SELECT order_source_id, orig_sys_document_ref
1950: FROM OE_HEADERS_IFACE_ALL
1951: WHERE batch_id = p_batch_id)
1952: AND c.IB_CURRENT_LOCATION_CODE IS NULL
1953: AND c.IB_CURRENT_LOCATION IS NOT NULL;
1954:

Line 2002: FROM OE_HEADERS_IFACE_ALL

1998: AND nvl(b.VERSION_NO,'x') = nvl(a.version_number,'x')
1999: )
2000: WHERE (order_source_id, orig_sys_document_ref) IN
2001: ( SELECT order_source_id, orig_sys_document_ref
2002: FROM OE_HEADERS_IFACE_ALL
2003: WHERE batch_id = p_batch_id)
2004: AND a.LIST_HEADER_ID IS NULL
2005: AND a.LIST_NAME IS NOT NULL;
2006:

Line 2017: FROM OE_HEADERS_IFACE_ALL

2013: WHERE b.LIST_HEADER_ID = a.LIST_HEADER_ID
2014: AND b.LIST_LINE_NO = a.LIST_LINE_NUMBER)
2015: WHERE (order_source_id, orig_sys_document_ref) IN
2016: ( SELECT order_source_id, orig_sys_document_ref
2017: FROM OE_HEADERS_IFACE_ALL
2018: WHERE batch_id = p_batch_id)
2019: AND a.LIST_LINE_ID IS NULL
2020: AND a.LIST_HEADER_ID IS NOT NULL
2021: AND a.LIST_LINE_NUMBER IS NOT NULL;

Line 2045: FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h

2041: ,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_LINE_TYPE_CODE'
2042: ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2043: ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2044: ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2045: FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2046: WHERE h.batch_id = p_batch_id
2047: AND a.order_source_id = h.order_source_id
2048: AND a.orig_sys_document_ref = h.orig_sys_document_ref
2049: AND (a.list_line_type_code NOT IN ('DIS','FREIGHT_CHARGE','SUR','PBH')

Line 2101: FROM OE_HEADERS_IFACE_ALL

2097: attribute_status
2098: FROM OE_LINES_IFACE_ALL
2099: WHERE (order_source_id, orig_sys_document_ref) IN
2100: ( SELECT order_source_id, orig_sys_document_ref
2101: FROM OE_HEADERS_IFACE_ALL
2102: WHERE batch_id = p_batch_id)
2103: AND attribute_status IS NOT NULL
2104: UNION
2105: SELECT request_id ,

Line 2112: FROM OE_HEADERS_IFACE_ALL

2108: NULL,
2109: NULL ,
2110: change_sequence,
2111: attribute_status
2112: FROM OE_HEADERS_IFACE_ALL
2113: WHERE batch_id = p_batch_id
2114: AND attribute_status IS NOT NULL;
2115:
2116: l_counter NUMBER :=0;