--SET serveroutput ON SIZE 1000000;

DECLARE

-------------------------------------------------------------------------------

-- PURPOSE:

-- Updates GMIR Bom_Header salesorder fields from decoded so_status in MLS

--

-- THEORY OF OPERATION:

-- This script contains an anomymous PL/SQL block built around a major and minor loop.

-- The major loop contains qualified BOM_HEADERS and the minor loop finds their salesorder

-- update vaules from SALES_ORDERS.so_status. The minor loop issues update statements

-- against BOM_HEADERS.

--

-- REVISIONS:

-- Code maintainers should be familiar with MLS-GMIR-SO_Updates.doc

-- before attempting any maintenance activities.

--

-- 02-FEB-05 JJM * store existing BOM_HEADER.salesorder in BOM_HEADER_HISTORY.

-- * better comparisons with NULL.

--

-- 26-JAN-05 JJM * EXECUTE IMMEDIATE now uses bound varibles for UPDATE targets.

-- * Commit every l_commit_rate (10) updates.

-- * Dont' update BOM_HEADERS.salesorders if the update will not

-- change the existing value.

--

-- 17-JAN-05 JJM Incorporate edits from walk thru. See MLS-GMIR-SO-Updates.doc

--

-- 14-JAN-05 JJM Started from concepts by K. Newbern, et al.

-------------------------------------------------------------------------------

update_value VARCHAR2(256);

update_stmt VARCHAR2(1024);

l_historyid NUMBER;

l_record_count NUMBER DEFAULT 0;

l_commit_rate NUMBER DEFAULT 100;

BEGIN

-- find qualified BOM_HEADERS since yesterday

FOR BOM_HEADERS IN

(SELECT bh.salesorder, bh.sitecode, bh.expansionid,

bh.erid, bh.componentttype, bh.current_config1900,

bh.target_config1900, bh.current_config850, bh.target_config850,

bh.status, bh.application, bh.fileid, bh.lastupdt, bh.lastupdtuser

FROM ct2user.BOM_HEADER bh,

SALES_ORDERS@x018 so

WHERE bh.sitecode || bh.expansionid = so.customer_reference||trim(substr(so.customer_order_id,-2,2))

AND to_date(so.modified_date) >= to_date(sysdate) - 1)

LOOP

BEGIN

-- get ALL the Sales Orders for each BOM_HEADER

FOR SALESORDERS IN

(select so.pk_case_id as SALESORDER, so.sellers_reference,

decode(upper(so.so_status),

'CANCELLED', 'CNCL',

'CLOSED', 'CLSD',

'DELIVERED', 'DLVD',

'DRAFT', 'DRFT',

'INVOICED', 'INVC',

'OPEN', 'OPEN',

'PARTIALLY DELIVERED', 'PDLV',

'SOURCING CONFIRMED', 'SCON',

'SOURCING DONE', 'SDON',

so.so_status) STATUS

from SALES_ORDERS@x018 so

where so.customer_reference || trim(substr(so.customer_order_id, -2, 2)) =

BOM_HEADERS.sitecode || BOM_HEADERS.expansionid)

LOOP

-- prefix update_value if 'NOT FOR INVOICING' within BOM_HEADER.salesorder

IF (instr(BOM_HEADERS.salesorder, 'NOT FOR INVOICING') > 0) THEN

IF (instr(update_value,'NOT FOR INVOICING') = 0) THEN -- only prefix it once.

update_value := update_value || 'NOT FOR INVOICING, ';

END IF;

END IF;

-- prefix update_value if RTN within ANY sales_order

IF (instr(SALESORDERS.sellers_reference, 'RTN', -3) > 0) THEN

IF (substr(NVL(update_value,' '),1,3) <> 'RTN') THEN -- only prefix it once.

update_value := 'RTN, ';

END IF;

END IF;

-- annotate salesorders with status if needed

IF NVL(instr(update_value, SALESORDERS.SALESORDER || SALESORDERS.STATUS), 0) = 0 THEN

update_value := update_value || SALESORDERS.SALESORDER || SALESORDERS.STATUS || ', ';

END IF;

END LOOP;

-- clip off any trailing comma

IF (INSTR(trim(update_value),',',-1) > 0) THEN

update_value := substr(trim(update_value),1,length(update_value)-2);

END IF;

-- update BOM_HEADER salesorder if needed

IF (update_value <> nvl(BOM_HEADERS.salesorder,' ')) THEN

-- save BOM_HEADER.salesorder before update in BOM_HEADER_HISTORY

SELECT bom_header_history_s.nextval INTO l_historyid FROM DUAL;

update_stmt := 'INSERT INTO ct2user.BOM_HEADER_HISTORY VALUES (' ||

l_historyid || ', ''' ||

BOM_HEADERS.erid || ''', ''' ||

BOM_HEADERS.sitecode || ''', ' ||

BOM_HEADERS.expansionid || ', ''' ||

BOM_HEADERS.componentttype || ''', ''' ||

BOM_HEADERS.current_config1900 || ''', ''' ||

BOM_HEADERS.target_config1900 || ''', ''' ||

BOM_HEADERS.current_config850 || ''', ''' ||

BOM_HEADERS.target_config850 || ''', ''' ||

BOM_HEADERS.status || ''', ''' ||

BOM_HEADERS.salesorder || ''', ';

--build an insert string that contains

--NULL keyword. allow fileid 0 to pass.

IF (nvl(BOM_HEADERS.fileid,-99) = -99) THEN

update_stmt := update_stmt || 'NULL';

ELSE

update_stmt := update_stmt || BOM_HEADERS.fileid;

END IF;

update_stmt := update_stmt || ', ''' ||

BOM_HEADERS.application || ''', ''' ||

BOM_HEADERS.lastupdt || ''', ''' ||

BOM_HEADERS.lastupdtuser || ''', ''' ||

sysdate || ''')';

EXECUTE IMMEDIATE update_stmt;

l_record_count := l_record_count + 1;

update_stmt := ' UPDATE ct2user.bom_header SET salesorder = ''' || update_value || ''' ' ||

' WHERE sitecode = ''' || BOM_HEADERS.sitecode || '''' ||

' AND expansionid = ' || BOM_HEADERS.expansionid;

EXECUTE IMMEDIATE update_stmt;

l_record_count := l_record_count + 1;

IF (MOD(l_record_count, l_commit_rate) = 0) THEN

COMMIT;

DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE('Committed ' || l_record_count || ' updates');

DBMS_OUTPUT.NEW_LINE;

END IF;

END IF;

-- reset inner loop values

update_value := '';

update_stmt := '';

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line (update_stmt);

dbms_output.put_line('Exception while processing SITE/EXPANSION: '

|| BOM_HEADERS.sitecode || BOM_HEADERS.expansionid ||

' NEW SALESORDER: ' || update_value ||

' SQLCODE: ' ||

SQLCODE ||

': ' ||

SQLERRM);

-- reset inner loop values

update_value := '';

update_stmt := '';

END;

END LOOP;

COMMIT;

END;