[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V CON
, HZ_CUST_ACCT_SITES CAS
, HZ_CUST_SITE_USES_ALL SITE
WHERE CON.NAME = p_contact
AND CON.CUSTOMER_ID = CAS.CUST_ACCOUNT_ID
AND CAS.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND CON.STATUS = 'A'
AND SITE.SITE_USE_ID = p_site_use_id;
SELECT site_use.site_use_id
FROM hz_locations loc,
hz_party_sites site,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_use
WHERE
site_use.cust_acct_site_id=acct_site.cust_acct_site_id
and acct_site.party_site_id=site.party_site_id
and site.location_id=loc.location_id
and site_use.status='A'
and acct_site.status='A' --bug 2752321
and acct_site.cust_account_id=in_end_customer_id
and loc.address1 = p_end_customer_site_address1
and nvl( loc.address2, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
and nvl( loc.address3, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
and nvl( loc.address4, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
and nvl( loc.city, fnd_api.g_miss_char) =
nvl( p_end_customer_site_city, fnd_api.g_miss_char)
and nvl( loc.state, fnd_api.g_miss_char) =
nvl( p_end_customer_site_state, fnd_api.g_miss_char)
and nvl( loc.postal_code, fnd_api.g_miss_char) =
nvl( p_end_customer_site_postalcode, fnd_api.g_miss_char)
and nvl( loc.country, fnd_api.g_miss_char) =
nvl( p_end_customer_site_country, fnd_api.g_miss_char)
and site_use.site_use_code = in_end_customer_site_use_code;
SELECT site_use.site_use_id
FROM hz_locations loc,
hz_party_sites site,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_use
WHERE loc.ADDRESS1 = p_end_customer_site_address1
AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
AND site_use.status = 'A'
AND acct_site.status ='A' --bug 2752321
AND acct_site.cust_account_id = p_end_customer_id
and site_use.site_use_code=in_end_customer_site_use_code
and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
and site.party_site_id=acct_site.party_site_id
and site.location_id=loc.location_id;
SELECT CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V
WHERE NAME = p_end_customer_contact
AND CUSTOMER_ID = p_end_customer_id;
UPDATE OE_HEADERS_IFACE_ALL c
SET (ORDER_TYPE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.ORDER_TYPE_ID,
DECODE(b.ORDER_TYPE_ID,NULL,'Y',NULL),
DECODE(b.ORDER_TYPE_ID,NULL,
c.ATTRIBUTE_STATUS||'002',c.ATTRIBUTE_STATUS)
FROM OE_ORDER_TYPES_V b, OE_HEADERS_IFACE_ALL d
WHERE d.order_type = b.name(+)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.ORDER_TYPE_ID IS NULL
AND c.order_type IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (price_list_id,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LIST_HEADER_ID,
DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
DECODE(b.LIST_HEADER_ID,NULL,
c.ATTRIBUTE_STATUS||'003',c.ATTRIBUTE_STATUS)
FROM qp_list_headers_vl b, OE_HEADERS_IFACE_ALL d
WHERE d.price_list = b.name(+)
AND NVL(b.list_type_code,'PRL') IN ('PRL', 'AGR')
AND nvl(b.active_flag,'Y') = 'Y'
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.price_list_id IS NULL
AND c.price_list IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (CONVERSION_TYPE_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CONVERSION_TYPE,
DECODE(b.CONVERSION_TYPE,NULL,'Y',NULL),
DECODE(b.CONVERSION_TYPE,NULL,
c.ATTRIBUTE_STATUS||'004',c.ATTRIBUTE_STATUS)
FROM OE_GL_DAILY_CONVERSION_TYPES_V b, OE_HEADERS_IFACE_ALL d
WHERE d.CONVERSION_TYPE = b.USER_CONVERSION_TYPE(+)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.CONVERSION_TYPE_CODE IS NULL
AND c.CONVERSION_TYPE IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SALESREP_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
DECODE(b.SALESREP_ID,NULL,'Y',NULL),
DECODE(b.SALESREP_ID,NULL,
c.ATTRIBUTE_STATUS||'005',c.ATTRIBUTE_STATUS)
FROM RA_SALESREPS b, OE_HEADERS_IFACE_ALL d
WHERE d.SALESREP = b.NAME(+)
AND sysdate between NVL(start_date_active,sysdate)
and NVL(end_date_active,sysdate)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SALESREP_ID IS NULL
AND c.SALESREP IS NOT NULL;
/* UPDATE OE_HEADERS_IFACE_ALL c
SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'TAX_REASON'
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.TAX_EXEMPT_REASON_CODE IS NULL
AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
UPDATE OE_HEADERS_IFACE_ALL c
SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
FROM FND_LOOKUPS b, OE_HEADERS_IFACE_ALL d
WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'ZX_EXEMPTION_REASON_CODE'
AND d.rowid = c.rowid)
WHERE c.batch_id = p_batch_id
AND c.TAX_EXEMPT_REASON_CODE IS NULL
AND c.TAX_EXEMPT_REASON IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (AGREEMENT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.AGREEMENT_ID,
DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
DECODE(b.AGREEMENT_ID,NULL,
c.ATTRIBUTE_STATUS||'007',c.ATTRIBUTE_STATUS)
FROM OE_AGREEMENTS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.AGREEMENT = b.NAME(+)
AND sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.AGREEMENT_ID IS NULL
AND c.AGREEMENT IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (INVOICING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.RULE_ID,
DECODE(b.RULE_ID,NULL,'Y',NULL),
DECODE(b.RULE_ID,NULL,
c.ATTRIBUTE_STATUS||'008',c.ATTRIBUTE_STATUS)
FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
WHERE d.INVOICING_RULE = b.NAME(+)
AND b.STATUS(+) = 'A'
AND b.TYPE(+) = 'I'
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.INVOICING_RULE_ID IS NULL
AND c.INVOICING_RULE IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (ACCOUNTING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.RULE_ID,
DECODE(b.RULE_ID,NULL,'Y',NULL),
DECODE(b.RULE_ID,NULL,
c.ATTRIBUTE_STATUS||'009',c.ATTRIBUTE_STATUS)
FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
WHERE d.ACCOUNTING_RULE = b.NAME(+)
AND b.STATUS(+) = 'A'
-- AND b.TYPE(+) = 'A' --we now allow variable accounting rules
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.ACCOUNTING_RULE_ID IS NULL
AND c.ACCOUNTING_RULE IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (PAYMENT_TERM_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.TERM_ID,
DECODE(b.TERM_ID,NULL,'Y',NULL),
DECODE(b.TERM_ID,NULL,
c.ATTRIBUTE_STATUS||'010',c.ATTRIBUTE_STATUS)
FROM OE_RA_TERMS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.PAYMENT_TERM = b.NAME(+)
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.PAYMENT_TERM_ID IS NULL
AND c.PAYMENT_TERM IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (FREIGHT_TERMS_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'011',c.ATTRIBUTE_STATUS)
FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
WHERE d.FREIGHT_TERMS = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.FREIGHT_TERMS_CODE IS NULL
AND c.FREIGHT_TERMS IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (FOB_POINT_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'012',c.ATTRIBUTE_STATUS)
FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.FOB_POINT = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'FOB'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.FOB_POINT_CODE IS NULL
AND c.FOB_POINT IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SOLD_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
WHERE d.CUSTOMER_NUMBER IS NOT NULL
AND d.CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A'
AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
WHERE d.CUSTOMER_NUMBER IS NULL
AND d.SOLD_TO_ORG = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A'
AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SOLD_TO_ORG_ID IS NULL
AND (c.SOLD_TO_ORG IS NOT NULL OR
c.CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_HEADERS_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'013'
WHERE c.batch_id = p_batch_id
AND c.SOLD_TO_ORG_ID IS NULL
AND c.SOLD_TO_ORG IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SHIP_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL
AND d.SHIP_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A'
AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NULL
AND d.SHIP_TO_CUSTOMER = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A'
AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SHIP_TO_CUSTOMER_ID IS NULL
AND (c.SHIP_TO_CUSTOMER IS NOT NULL OR
c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_HEADERS_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'014'
WHERE c.batch_id = p_batch_id
AND c.SHIP_TO_CUSTOMER_ID IS NULL
AND c.SHIP_TO_CUSTOMER IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (INVOICE_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
WHERE d.INVOICE_CUSTOMER_NUMBER IS NOT NULL
AND d.INVOICE_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A'
AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
WHERE d.INVOICE_CUSTOMER_NUMBER IS NULL
AND d.INVOICE_CUSTOMER = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A'
AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.INVOICE_CUSTOMER_ID IS NULL
AND (c.INVOICE_CUSTOMER IS NOT NULL OR
c.INVOICE_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_HEADERS_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
WHERE c.batch_id = p_batch_id
AND c.INVOICE_CUSTOMER_ID IS NULL
AND c.INVOICE_CUSTOMER IS NOT NULL ;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SHIP_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
DECODE(b.ORGANIZATION_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM OE_SHIP_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.ship_to_address1 = ADDRESS_LINE_1(+)
AND nvl(d.ship_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
AND nvl(d.ship_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
--AND nvl(d.ship_to_address4,'NIL') = ADDRESS_LINE_4
AND nvl(d.ship_to_city,'NIL') = nvl(town_or_city(+),'NIL')
AND nvl(d.ship_to_state,'NIL') = nvl(state(+),'NIL')
AND nvl(d.ship_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
AND nvl(d.ship_to_country,'NIL') = nvl(country(+),'NIL')
AND nvl(STATUS(+),'A') = 'A'
AND b.CUSTOMER_ID(+) = nvl(d.SHIP_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SHIP_TO_ORG_ID IS NULL
AND (c.SHIP_TO_ADDRESS1 IS NOT NULL
OR c.ship_to_address2 IS NOT NULL
OR c.ship_to_address3 IS NOT NULL
OR c.ship_to_address4 IS NOT NULL
OR c.ship_to_org IS NOT NULL
);
UPDATE OE_HEADERS_IFACE_ALL c
SET (INVOICE_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
DECODE(b.ORGANIZATION_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM OE_INVOICE_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.invoice_address1 = ADDRESS_LINE_1(+)
AND nvl(d.invoice_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
AND nvl(d.invoice_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
--AND nvl(d.invoice_address4,'NIL') = ADDRESS_LINE_4
AND nvl(d.invoice_city,'NIL') = nvl(town_or_city(+),'NIL')
AND nvl(d.invoice_state,'NIL') = nvl(state(+),'NIL')
AND nvl(d.invoice_postal_code,'NIL') = nvl(postal_code(+),'NIL')
AND nvl(d.invoice_country,'NIL') = nvl(country(+),'NIL')
AND nvl(STATUS(+),'A') = 'A'
AND b.CUSTOMER_ID(+) = nvl(d.INVOICE_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.INVOICE_TO_ORG_ID IS NULL
AND (c.invoice_address1 IS NOT NULL
OR c.invoice_address2 IS NOT NULL
OR c.invoice_address3 IS NOT NULL
OR c.invoice_address4 IS NOT NULL
OR c.invoice_to_org IS NOT NULL
);
UPDATE OE_HEADERS_IFACE_ALL c
SET (SOLD_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT DISTINCT b.CONTACT_ID,
DECODE(b.CONTACT_ID,NULL,'Y',NULL),
DECODE(b.CONTACT_ID,NULL,
c.ATTRIBUTE_STATUS||'016',c.ATTRIBUTE_STATUS)
FROM OE_CONTACTS_V b
WHERE c.SOLD_TO_CONTACT = b.NAME(+)
AND c.SOLD_TO_ORG_ID = b.CUSTOMER_ID(+)
)
WHERE c.batch_id = p_batch_id
AND c.SOLD_TO_CONTACT_ID IS NULL
AND c.SOLD_TO_CONTACT IS NOT NULL
AND c.SOLD_TO_ORG_ID IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SHIP_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),
DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'017',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE c.batch_id = p_batch_id
AND c.SHIP_TO_CONTACT_ID IS NULL
AND c.SHIP_TO_CONTACT IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (INVOICE_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),
DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'018',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE c.batch_id = p_batch_id
AND c.INVOICE_TO_CONTACT_ID IS NULL
AND c.INVOICE_TO_CONTACT IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (DELIVER_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),
DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'019',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE c.batch_id = p_batch_id
AND c.DELIVER_TO_CONTACT_ID IS NULL
AND c.DELIVER_TO_CONTACT IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SHIPPING_METHOD_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'024',c.ATTRIBUTE_STATUS)
FROM OE_SHIP_METHODS_V b, OE_HEADERS_IFACE_ALL d
WHERE d.SHIPPING_METHOD = b.MEANING(+)
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE(+), SYSDATE)
AND NVL(END_DATE_ACTIVE(+), SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SHIPPING_METHOD_CODE IS NULL
AND c.SHIPPING_METHOD IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SALES_CHANNEL_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'022',c.ATTRIBUTE_STATUS)
FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
WHERE d.SALES_CHANNEL = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SALES_CHANNEL_CODE IS NULL
AND c.SALES_CHANNEL IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SHIPMENT_PRIORITY_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'023',c.ATTRIBUTE_STATUS)
FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
WHERE d.SHIPMENT_PRIORITY = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE c.batch_id = p_batch_id
AND c.SHIPMENT_PRIORITY_CODE IS NULL
AND c.SHIPMENT_PRIORITY IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (SOLD_TO_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT SITE.SITE_USE_ID,
DECODE(SITE.SITE_USE_ID,null,'Y',null),
DECODE(SITE.SITE_USE_ID,null,d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
OE_HEADERS_IFACE_ALL d
WHERE SITE.SITE_USE_CODE ='SOLD_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND d.sold_to_location_address1= LOC.ADDRESS1(+)
AND nvl(d.sold_to_location_address2,'NIL')=nvl(LOC.ADDRESS2(+),'NIL')
AND nvl(d.sold_to_location_address3,'NIL')=nvl(LOC.ADDRESS3(+),'NIL')
AND nvl(d.sold_to_location_address4,'NIL')=nvl(LOC.ADDRESS4(+),'NIL')
AND nvl(d.sold_to_location_city,'NIL')=nvl(LOC.CITY(+),'NIL')
AND nvl(d.sold_to_location_state,'NIL')=nvl(LOC.STATE(+),'NIL')
AND nvl(d.sold_to_location_postal_code,'NIL')=nvl(LOC.POSTAL_CODE(+),'NIL')
AND nvl(d.sold_to_location_country,'NIL')=nvl(LOC.COUNTRY(+),'NIL')
AND nvl(SITE.STATUS,'A')='A'
AND nvl(ACCT_SITE.STATUS,'A')='A'
AND ACCT_SITE.CUST_ACCOUNT_ID(+)=d.SOLD_TO_ORG_ID
AND d.rowid=c.rowid
)
WHERE c.batch_id=p_batch_id
AND c.SOLD_TO_SITE_USE_ID IS NULL
AND (c.SOLD_TO_LOCATION_ADDRESS1 IS NOT NULL
OR c.SOLD_TO_LOCATION_ADDRESS2 IS NOT NULL
OR c.SOLD_TO_LOCATION_ADDRESS3 IS NOT NULL
OR c.SOLD_TO_LOCATION_ADDRESS4 IS NOT NULL
);
UPDATE OE_HEADERS_IFACE_ALL c
SET (END_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
WHERE d.END_CUSTOMER_NUMBER IS NOT NULL AND d.END_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A' AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS) FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
WHERE d.END_CUSTOMER_NUMBER IS NULL AND d.END_CUSTOMER_NAME = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid)
WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_ID IS NULL AND
(c.END_CUSTOMER_NAME IS NOT NULL OR c.END_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_HEADERS_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
WHERE c.batch_id = p_batch_id
AND c.END_CUSTOMER_ID IS NULL
AND c.END_CUSTOMER_NAME IS NOT NULL ;
UPDATE OE_HEADERS_IFACE_ALL c
SET (END_CUSTOMER_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT END_CUSTOMER_SITE(c.end_customer_address1,c.end_customer_address2,c.end_customer_address3,c.end_customer_address4,
c.end_customer_location, NULL,c.end_customer_id,c.end_customer_city,
c.end_customer_state,c.end_customer_postal_code,c.end_customer_country,NULL),
DECODE(END_CUSTOMER_SITE(c.end_customer_address1,c.end_customer_address2,c.end_customer_address3,
c.end_customer_address4,c.end_customer_location,NULL,
c.end_customer_id,c.end_customer_city,c.end_customer_state,
c.end_customer_postal_code,c.end_customer_country,
NULL),NULL,'Y',NULL),
DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
c.end_customer_address2,
c.end_customer_address3,
c.end_customer_address4,
c.end_customer_location,
NULL,
c.end_customer_id,
c.end_customer_city,
c.end_customer_state,
c.end_customer_postal_code,
c.end_customer_country,
NULL),NULL,c.ATTRIBUTE_STATUS||'027',c.ATTRIBUTE_STATUS)
FROM DUAL)
WHERE c.batch_id=p_batch_id
AND c.END_CUSTOMER_SITE_USE_ID IS NULL AND c.END_CUSTOMER_ID IS NOT NULL
AND (c.END_CUSTOMER_ADDRESS1 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS2 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS3 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS4 IS NOT NULL);
UPDATE OE_HEADERS_IFACE_ALL c
SET (END_CUSTOMER_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),
DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,'Y',NULL),
DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)
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;
UPDATE OE_HEADERS_IFACE_ALL c
SET (IB_OWNER_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'028',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_OWNER', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_HEADERS_IFACE_ALL d
WHERE d.IB_OWNER= b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_OWNER'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
--AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE c.batch_id = p_batch_id
AND c.IB_OWNER_CODE IS NULL AND c.IB_OWNER IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (IB_INSTALLED_AT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'029',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_INSTALL_LOCATION', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_HEADERS_IFACE_ALL d
WHERE d.IB_INSTALLED_AT_LOCATION = b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_INSTALL_LOCATION'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
--AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE c.batch_id = p_batch_id
AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
UPDATE OE_HEADERS_IFACE_ALL c
SET (IB_CURRENT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'030',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_CURRENT_LOCATION', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_HEADERS_IFACE_ALL d
WHERE d.IB_CURRENT_LOCATION = b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_CURRENT_LOCATION'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
-- AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE c.batch_id = p_batch_id
AND c.IB_CURRENT_LOCATION_CODE IS NULL
AND c.IB_CURRENT_LOCATION IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (LINE_TYPE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.transaction_type_id,
DECODE(b.transaction_type_id,NULL,'Y',NULL),
DECODE(b.transaction_type_id,NULL,
c.ATTRIBUTE_STATUS||'020',c.ATTRIBUTE_STATUS)
FROM OE_TRANSACTION_TYPES_TL b, OE_LINES_IFACE_ALL d
WHERE d.LINE_TYPE = b.name(+)
AND NVL(b.LANGUAGE,USERENV('LANG')) = USERENV('LANG')
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.LINE_TYPE_ID IS NULL
AND c.LINE_TYPE IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (price_list_id,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LIST_HEADER_ID,
DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
DECODE(b.LIST_HEADER_ID,NULL,
c.ATTRIBUTE_STATUS||'003',c.ATTRIBUTE_STATUS)
FROM qp_list_headers_vl b, OE_LINES_IFACE_ALL d
WHERE d.price_list = b.name(+)
AND NVL(b.list_type_code,'PRL') IN ('PRL', 'AGR')
AND nvl(b.active_flag,'Y') = 'Y'
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.price_list_id IS NULL
AND c.price_list IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET ( SALESREP_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
DECODE(b.SALESREP_ID,NULL,'Y',NULL),
DECODE(b.SALESREP_ID,NULL,
c.ATTRIBUTE_STATUS||'005',c.ATTRIBUTE_STATUS)
FROM RA_SALESREPS b, OE_LINES_IFACE_ALL d
WHERE d.SALESREP = b.NAME(+)
AND sysdate between NVL(start_date_active,sysdate)
and NVL(end_date_active,sysdate)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SALESREP_ID IS NULL
AND c.SALESREP IS NOT NULL;
/*UPDATE OE_LINES_IFACE_ALL c
SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
FROM OE_AR_LOOKUPS_V b, OE_LINES_IFACE_ALL d
WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'TAX_REASON'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.TAX_EXEMPT_REASON_CODE IS NULL
AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
UPDATE OE_LINES_IFACE_ALL c
SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
FROM FND_LOOKUPS b, OE_LINES_IFACE_ALL d
WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'ZX_EXEMPTION_REASON_CODE'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid)
WHERE (order_source_id, orig_sys_document_ref) IN
(SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.TAX_EXEMPT_REASON_CODE IS NULL
AND c.TAX_EXEMPT_REASON IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (AGREEMENT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.AGREEMENT_ID,
DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
DECODE(b.AGREEMENT_ID,NULL,
c.ATTRIBUTE_STATUS||'007',c.ATTRIBUTE_STATUS)
FROM OE_AGREEMENTS_V b, OE_LINES_IFACE_ALL d
WHERE d.AGREEMENT = b.NAME(+)
AND sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.AGREEMENT_ID IS NULL
AND c.AGREEMENT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (INVOICING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.RULE_ID,
DECODE(b.RULE_ID,NULL,'Y',NULL),
DECODE(b.RULE_ID,NULL,
c.ATTRIBUTE_STATUS||'008',c.ATTRIBUTE_STATUS)
FROM OE_RA_RULES_V b, OE_LINES_IFACE_ALL d
WHERE d.INVOICING_RULE = b.NAME(+)
AND b.STATUS(+) = 'A'
AND b.TYPE(+) = 'I'
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.INVOICING_RULE_ID IS NULL
AND c.INVOICING_RULE IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (ACCOUNTING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.RULE_ID,
DECODE(b.RULE_ID,NULL,'Y',NULL),
DECODE(b.RULE_ID,NULL,
c.ATTRIBUTE_STATUS||'009',c.ATTRIBUTE_STATUS)
FROM OE_RA_RULES_V b, OE_LINES_IFACE_ALL d
WHERE d.ACCOUNTING_RULE = b.NAME(+)
AND b.STATUS(+) = 'A'
-- AND b.TYPE(+) = 'A' --we now allow variable accounting rules
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.ACCOUNTING_RULE_ID IS NULL
AND c.ACCOUNTING_RULE IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (PAYMENT_TERM_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.TERM_ID,
DECODE(b.TERM_ID,NULL,'Y',NULL),
DECODE(b.TERM_ID,NULL,
c.ATTRIBUTE_STATUS||'010',c.ATTRIBUTE_STATUS)
FROM OE_RA_TERMS_V b, OE_LINES_IFACE_ALL d
WHERE d.PAYMENT_TERM = b.NAME(+)
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.PAYMENT_TERM_ID IS NULL
AND c.PAYMENT_TERM IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (FREIGHT_TERMS_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'011',c.ATTRIBUTE_STATUS)
FROM OE_LOOKUPS b, OE_LINES_IFACE_ALL d
WHERE d.FREIGHT_TERMS = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.FREIGHT_TERMS_CODE IS NULL
AND c.FREIGHT_TERMS IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (FOB_POINT_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'012',c.ATTRIBUTE_STATUS)
FROM OE_AR_LOOKUPS_V b, OE_LINES_IFACE_ALL d
WHERE d.FOB_POINT = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'FOB'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.FOB_POINT_CODE IS NULL
AND c.FOB_POINT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET SOLD_TO_ORG_ID =
(SELECT d.SOLD_TO_ORG_ID FROM OE_HEADERS_IFACE_ALL d, OE_LINES_IFACE_ALL e
WHERE d.order_source_id = e.order_source_id
AND d.orig_sys_document_ref = e.orig_sys_document_ref
AND batch_id = p_batch_id
AND d.sold_to_org_id IS NOT NULL
AND c.rowid = e.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id
)
AND c.SOLD_TO_ORG_ID IS NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (SHIP_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL
AND d.SHIP_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A'
AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NULL
AND d.SHIP_TO_CUSTOMER_NAME = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A'
AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SHIP_TO_CUSTOMER_ID IS NULL
AND (c.SHIP_TO_CUSTOMER_NAME IS NOT NULL OR
c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_LINES_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'014'
WHERE c.SHIP_TO_CUSTOMER_ID IS NULL
AND c.SHIP_TO_CUSTOMER_NAME IS NOT NULL
AND (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id) ;
UPDATE OE_LINES_IFACE_ALL c
SET (INVOICE_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
WHERE d.INVOICE_TO_CUSTOMER_NUMBER IS NOT NULL
AND d.INVOICE_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A'
AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
WHERE d.INVOICE_TO_CUSTOMER_NUMBER IS NULL
AND d.INVOICE_TO_CUSTOMER_NAME = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A'
AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.INVOICE_TO_CUSTOMER_ID IS NULL
AND (c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL OR
c.INVOICE_TO_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_LINES_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.INVOICE_TO_CUSTOMER_ID IS NULL
AND c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL ;
UPDATE OE_LINES_IFACE_ALL c
SET (SHIP_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
DECODE(b.ORGANIZATION_ID,NULL,
d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
FROM OE_SHIP_TO_ORGS_V b, OE_LINES_IFACE_ALL d
WHERE d.ship_to_address1 = ADDRESS_LINE_1(+)
AND nvl(d.ship_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
AND nvl(d.ship_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
--AND nvl(d.ship_to_address4,'NIL') = ADDRESS_LINE_4
AND nvl(d.ship_to_city,'NIL') = nvl(town_or_city(+),'NIL')
AND nvl(d.ship_to_state,'NIL') = nvl(state(+),'NIL')
AND nvl(d.ship_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
AND nvl(d.ship_to_country,'NIL') = nvl(country(+),'NIL')
AND nvl(STATUS(+),'A') = 'A'
AND b.CUSTOMER_ID(+) = nvl(d.SHIP_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SHIP_TO_ORG_ID IS NULL
AND (c.SHIP_TO_ADDRESS1 IS NOT NULL
OR c.ship_to_address2 IS NOT NULL
OR c.ship_to_address3 IS NOT NULL
OR c.ship_to_address4 IS NOT NULL
OR c.ship_to_org IS NOT NULL
);
UPDATE OE_LINES_IFACE_ALL c
SET (INVOICE_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
DECODE(b.ORGANIZATION_ID,NULL,
d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
FROM OE_INVOICE_TO_ORGS_V b, OE_LINES_IFACE_ALL d
WHERE d.invoice_to_address1 = ADDRESS_LINE_1(+)
AND nvl(d.invoice_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
AND nvl(d.invoice_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
--AND nvl(d.invoice_to_address4,'NIL') = ADDRESS_LINE_4
AND nvl(d.invoice_to_city,'NIL') = nvl(town_or_city(+),'NIL')
AND nvl(d.invoice_to_state,'NIL') = nvl(state(+),'NIL')
AND nvl(d.invoice_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
AND nvl(d.invoice_to_country,'NIL') = nvl(country(+),'NIL')
AND nvl(STATUS(+),'A') = 'A'
AND b.CUSTOMER_ID(+) = nvl(d.INVOICE_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.INVOICE_TO_ORG_ID IS NULL
AND (c.invoice_to_address1 IS NOT NULL
OR c.invoice_to_address2 IS NOT NULL
OR c.invoice_to_address3 IS NOT NULL
OR c.invoice_to_address4 IS NOT NULL
OR c.invoice_to_org IS NOT NULL
);
UPDATE OE_LINES_IFACE_ALL c
SET (SHIP_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),
DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'017',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SHIP_TO_CONTACT_ID IS NULL
AND c.SHIP_TO_CONTACT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (INVOICE_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),
DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'018',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.INVOICE_TO_CONTACT_ID IS NULL
AND c.INVOICE_TO_CONTACT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (DELIVER_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),
DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,'Y',NULL),
DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,
c.ATTRIBUTE_STATUS||'019',c.ATTRIBUTE_STATUS)
FROM DUAL
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.DELIVER_TO_CONTACT_ID IS NULL
AND c.DELIVER_TO_CONTACT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (SHIPMENT_PRIORITY_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'023',c.ATTRIBUTE_STATUS)
FROM OE_LOOKUPS b, OE_LINES_IFACE_ALL d
WHERE d.SHIPMENT_PRIORITY = b.MEANING(+)
AND b.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SHIPMENT_PRIORITY_CODE IS NULL
AND c.SHIPMENT_PRIORITY IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (SHIPPING_METHOD_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'024',c.ATTRIBUTE_STATUS)
FROM OE_SHIP_METHODS_V b, OE_LINES_IFACE_ALL d
WHERE d.SHIPPING_METHOD = b.MEANING(+)
AND b.enabled_flag(+) = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.SHIPPING_METHOD_CODE IS NULL
AND c.SHIPPING_METHOD IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (END_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
WHERE d.END_CUSTOMER_NUMBER IS NOT NULL
AND d.END_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
AND b.STATUS(+) = 'A' AND d.rowid = c.rowid
UNION ALL
SELECT b.CUST_ACCOUNT_ID,
DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
DECODE(b.CUST_ACCOUNT_ID,NULL,
d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
WHERE d.END_CUSTOMER_NUMBER IS NULL
AND d.END_CUSTOMER_NAME = e.PARTY_NAME(+)
AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
AND d.rowid = c.rowid)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.END_CUSTOMER_ID IS NULL
AND (c.END_CUSTOMER_NAME IS NOT NULL
OR c.END_CUSTOMER_NUMBER IS NOT NULL);
UPDATE OE_LINES_IFACE_ALL c
SET ERROR_FLAG = 'Y',
ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.END_CUSTOMER_ID IS NULL
AND c.END_CUSTOMER_NAME IS NOT NULL ;
UPDATE OE_LINES_IFACE_ALL c
SET (END_CUSTOMER_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT
Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),
DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,
c.END_CUSTOMER_ID),NULL,'Y',NULL),
DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,
c.END_CUSTOMER_ID),NULL,
c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)
FROM DUAL)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.END_CUSTOMER_CONTACT_ID IS NULL
AND c.END_CUSTOMER_CONTACT IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (END_CUSTOMER_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT END_CUSTOMER_SITE(c.end_customer_address1,
c.end_customer_address2,c.end_customer_address3,
c.end_customer_address4,c.end_customer_location, NULL,
c.end_customer_id,c.end_customer_city, c.end_customer_state,
c.end_customer_postal_code,c.end_customer_country,NULL),
DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
c.end_customer_address2,c.end_customer_address3,
c.end_customer_address4,c.end_customer_location,NULL,
c.end_customer_id,c.end_customer_city,
c.end_customer_state, c.end_customer_postal_code,
c.end_customer_country, NULL),NULL,'Y',NULL),
DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
c.end_customer_address2,
c.end_customer_address3,
c.end_customer_address4,
c.end_customer_location,
NULL,
c.end_customer_id,
c.end_customer_city,
c.end_customer_state,
c.end_customer_postal_code,
c.end_customer_country,
NULL),
NULL,c.ATTRIBUTE_STATUS||'027',c.ATTRIBUTE_STATUS)
FROM DUAL)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.END_CUSTOMER_SITE_USE_ID IS NULL AND c.END_CUSTOMER_ID IS NOT NULL
AND (c.END_CUSTOMER_ADDRESS1 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS2 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS3 IS NOT NULL
OR c.END_CUSTOMER_ADDRESS4 IS NOT NULL);
UPDATE OE_LINES_IFACE_ALL c
SET (IB_OWNER_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'028',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_OWNER', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_LINES_IFACE_ALL d
WHERE d.IB_OWNER = b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_OWNER'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
-- AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.IB_OWNER_CODE IS NULL
AND c.IB_OWNER IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (IB_INSTALLED_AT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'029',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_INSTALL_LOCATION', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_LINES_IFACE_ALL d
WHERE d.IB_INSTALLED_AT_LOCATION = b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_INSTALL_LOCATION'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
-- AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
UPDATE OE_LINES_IFACE_ALL c
SET (IB_CURRENT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
(SELECT b.LOOKUP_CODE,
DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
DECODE(b.LOOKUP_CODE,NULL,
c.ATTRIBUTE_STATUS||'030',c.ATTRIBUTE_STATUS)
FROM -- OE_LOOKUPS b,
( select lookup_code, meaning
from OE_LOOKUPS
where lookup_type In ('ITEM_CURRENT_LOCATION', 'ONT_INSTALL_BASE')
and enabled_flag = 'Y'
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
OE_LINES_IFACE_ALL d
WHERE d.IB_CURRENT_LOCATION = b.MEANING(+)
--AND ( b.LOOKUP_TYPE(+) = 'ITEM_CURRENT_LOCATION'
--OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
--AND b.enabled_flag(+) = 'Y'
--AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
-- AND NVL(END_DATE_ACTIVE, SYSDATE)
AND d.rowid = c.rowid
AND rownum = 1
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND c.IB_CURRENT_LOCATION_CODE IS NULL
AND c.IB_CURRENT_LOCATION IS NOT NULL;
UPDATE OE_PRICE_ADJS_INTERFACE a
SET (LIST_HEADER_ID, ERROR_FLAG) =
(SELECT b.list_header_id
, decode(b.list_header_id,NULL,'Y',NULL)
FROM QP_LIST_HEADERS_TL b
WHERE b.NAME = a.list_name
AND b.LANGUAGE = userenv('LANG')
AND nvl(b.VERSION_NO,'x') = nvl(a.version_number,'x')
)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND a.LIST_HEADER_ID IS NULL
AND a.LIST_NAME IS NOT NULL;
UPDATE OE_PRICE_ADJS_INTERFACE a
SET (LIST_LINE_ID, LIST_LINE_TYPE_CODE, ERROR_FLAG) =
(SELECT b.list_line_id
, b.list_line_type_code
, decode(b.list_line_id,NULL,'Y',NULL)
FROM QP_LIST_LINES b
WHERE b.LIST_HEADER_ID = a.LIST_HEADER_ID
AND b.LIST_LINE_NO = a.LIST_LINE_NUMBER)
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND a.LIST_LINE_ID IS NULL
AND a.LIST_HEADER_ID IS NOT NULL
AND a.LIST_LINE_NUMBER IS NOT NULL;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
a.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_LINE_TYPE_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND (a.list_line_type_code NOT IN ('DIS','FREIGHT_CHARGE','SUR','PBH')
OR (a.list_header_id IS NULL OR a.list_line_id IS NULL));
PROCEDURE INSERT_ERROR_MESSAGES(p_batch_id NUMBER)
IS
CURSOR C_ERR IS
SELECT request_id ,
order_source_id ,
orig_sys_document_ref ,
orig_sys_line_ref,
orig_sys_shipment_ref ,
change_sequence,
attribute_status
FROM OE_LINES_IFACE_ALL
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND attribute_status IS NOT NULL
UNION
SELECT request_id ,
order_source_id ,
orig_sys_document_ref ,
NULL,
NULL ,
change_sequence,
attribute_status
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id
AND attribute_status IS NOT NULL;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref
,change_sequence ,source_document_type_id ,source_document_id
,source_document_line_id ,attribute_code ,creation_date
,created_by ,last_update_date ,last_updated_by ,last_update_login
,program_application_id ,program_id ,program_update_date
,process_activity ,notification_flag ,type ,message_source_code
,language ,message_text, transaction_id
)
VALUES
( l_err.request_id,DECODE(l_err.ORIG_SYS_LINE_REF,NULL,'HEADER','LINE')
, NULL ,NULL ,NULL ,NULL ,l_err.order_source_id
, l_err.orig_sys_document_ref , l_err.ORIG_SYS_LINE_REF
, l_err.orig_sys_shipment_ref , l_err.change_sequence ,NULL ,NULL
, NULL ,l_attribute, sysdate ,FND_GLOBAL.USER_ID ,sysdate
, FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL
, NULL ,NULL ,NULL ,'C' ,USERENV('LANG')
, l_msg_text, OE_MSG_ID_S.NEXTVAL
);
oe_debug_pub.add( 'OTHERS ERROR , OE_BULK_VALUE_TO_ID.INSERT_ERROR_MESSAGES' ) ;
, 'INSERT_ERROR_MESSAGES'
);
END INSERT_ERROR_MESSAGES;