[Home] [Help]
26: uFile_type utl_file.file_type;
27: p_output_width INTEGER := 4000;
28: p_run_id INTEGER;
29: p_header_interface VARCHAR2(120) := 'ECE_SPSO_HEADERS';
30: p_item_interface VARCHAR2(120) := 'ECE_SPSO_ITEMS';
31: p_item_d_interface VARCHAR2(120) := 'ECE_SPSO_ITEM_DET';
32: p_transaction_date DATE := SYSDATE;
33: xProgress VARCHAR2(30);
34: cEnabled VARCHAR2(1) := 'Y';
297: uFile_type utl_file.file_type;
298: p_output_width INTEGER := 4000;
299: p_run_id NUMBER ;
300: p_header_interface VARCHAR2(120) := 'ECE_SPSO_HEADERS';
301: p_item_interface VARCHAR2(120) := 'ECE_SPSO_ITEMS';
302: p_item_d_interface VARCHAR2(120) := 'ECE_SPSO_ITEM_DET';
303: p_transaction_date DATE := SYSDATE;
304: cEnabled VARCHAR2(1) := 'Y';
305: ece_transaction_disabled EXCEPTION;
840: IF cTransaction_Type = 'SPSO'
841: THEN
842:
843: cHeader_view := 'ECE_SPSO_HEADERS_V';
844: cItem_view := 'ECE_SPSO_ITEMS_V';
845:
846: ELSIF cTransaction_Type = 'SSSO'
847: THEN
848:
1313:
1314: xProgress := 'SPSOB-10-1500';
1315: /* Bug 1705597.
1316: Get item_id for the corresponding schedule_item_id
1317: from the view ece_spso_items_v and use this value
1318: in the following query to get asl_id and other data
1319: */
1320:
1321: begin
1519: END IF;
1520:
1521: BEGIN
1522: xProgress := 'SPSOB-10-1550';
1523: SELECT ece_spso_items_s.nextval
1524: INTO l_item_fkey
1525: FROM sys.dual;
1526: EXCEPTION
1527: WHEN NO_DATA_FOUND THEN
1530: 'ECE_GET_NEXT_SEQ_FAILED',
1531: 'PROGRESS_LEVEL',
1532: xProgress,
1533: 'SEQ',
1534: 'ECE_SPSO_ITEMS_S' );
1535: END;
1536:
1537: ec_debug.pl ( 3, 'l_item_fkey: ',l_item_fkey );
1538: xProgress := 'SPSOB-10-1551';
1556: p_source_tbl => l_item_tbl,
1557: p_foreign_key => l_item_fkey );
1558:
1559: xProgress := 'SPSOB-10-1570';
1560: UPDATE ece_spso_items
1561: SET supplier_product_number = x_supplier_product_num,
1562: item_scheduler_last_name = x_scheduler_last_name,
1563: item_scheduler_first_name = x_scheduler_first_name,
1564: item_scheduler_work_telephone = x_scheduler_work_telephone,
1641: xProgress,
1642: 'INFO',
1643: 'SUPPLIER_PRODUCT_NUMBER',
1644: 'TABLE_NAME',
1645: 'ECE_SPSO_ITEMS' );
1646: END IF;
1647:
1648: -- ******************************************
1649: --
1947: BEGIN
1948: xProgress := 'SPSOB-30-1030';
1949: SELECT transaction_record_id
1950: INTO x_transaction_record_id
1951: FROM ece_spso_items
1952: WHERE schedule_id = rec_item.schedule_id
1953: AND schedule_item_id = rec_item.schedule_item_id
1954: AND run_id = p_run_id;
1955: EXCEPTION
1961: xProgress,
1962: 'INFO',
1963: 'TRANSACTION_RECORD_ID',
1964: 'TABLE_NAME',
1965: 'ECE_SPSO_ITEMS' );
1966: END;
1967:
1968: ec_debug.pl ( 3, 'x_transaction_record_id: ',x_transaction_record_id );
1969:
2223: ec_debug.pl ( 3, 'x_shipment_num: ',x_shipment_num );
2224: ec_debug.pl ( 3, 'x_transaction_date: ',x_transaction_date );
2225: ec_debug.pl ( 3, 'x_last_quantity: ',x_last_quantity );
2226:
2227: UPDATE ece_spso_items
2228: SET last_receipt_shipment_code = x_shipment_num,
2229: last_receipt_date = x_transaction_date,
2230: last_receipt_quantity = x_last_quantity
2231: WHERE transaction_record_id = x_transaction_record_id;
2342: -- ***************************
2343:
2344: xProgress := 'SPSOB-30-1190';
2345: ec_debug.pl ( 3, 'rec_item.starting_cum_quantity: ',NVL(rec_item.starting_cum_quantity,0) );
2346: UPDATE ece_spso_items
2347: SET last_receipt_cum_qty = NVL(rec_item.starting_cum_quantity,0)
2348: WHERE transaction_record_id = x_transaction_record_id;
2349: END IF;
2350:
2514: p_transaction_type IN VARCHAR2, -- plan SPSO, ship SSSO
2515: p_output_width IN INTEGER,
2516: p_run_id IN INTEGER,
2517: p_header_interface IN VARCHAR2 := 'ECE_SPSO_HEADERS',
2518: p_item_interface IN VARCHAR2 := 'ECE_SPSO_ITEMS',
2519: p_item_d_interface IN VARCHAR2 := 'ECE_SPSO_ITEM_DET',
2520: p_ship_d_interface IN VARCHAR2 := 'ECE_SPSO_SHIP_DET')
2521: IS
2522: xProgress VARCHAR2(30);